subtotal and average

45 pts.
Tags:
AS/400 Query
Query/400
Can anyone tell me how/if I could using query400 take a list of records, subtotal and create a file with only the subtotals for export to excel? My file is all orders, with line items and $$ for each line. I need to subtotal the orders by order # and create a file listing only the customer #, order# and the order total for export to excel. Is there a way/how I can create a result field to hold the subtotal amount? I can sum the line item $$ but it puts it in its own row and breaks. My fields/columns are (A)Cust# (all the same customer #), (B)Order#, (C)part#, (D) line $$. Orders have a variety of line items. I have just over 5000 records. I want one file with (A)Customer#, (B)Order#, (C)Order total. Any tips or hints how I can accomplish this? Can it be done with query? Thanks, I appreciate any help.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

You’ll need to do something like the following :-

In report summary functions – specify total for your $$.

In report breaks – specify all the fields that you need in your file (including order#) at the same level (eg 1).

In output type and form – specify database file and summary only.

When you run your query it should create your file with one record per order# with an order total and a “break level” (what you specified in report breaks). It will also have a record with a “break level” of 0 which is the total for the complete query.

In your excel, you should be able to remove the break level 0 record (or your could use UPDDTA or STRSQL to remove it).

You can use CPYTOIMPF to convert the resulting file to .CSV for import into excel.

Regards,

Martin Gilbert.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following