Total amount in terms of specific variables in the script

[majid_ghaboli]

Hi

please guide me

I have table similar below :

Ord_No	Inv_No	Groupe_Code	Brand_Code	Good_Code	Quantity	Amount
AT9501	1	1	ATOMIC	AT_9501	50	1000000
AT9501	1	1	ATOMIC	AT_9506	100	2130000
AT9501	2	2	ATOMIC	AT_9588	120	2500000
NK9501	1	3	NIKE	NK_3288	200	3200000
NK9501	1	3	NIKE	NK_6528	180	5200000
NK9501	2	4	NIKE	NK_1478	200	4100000
SA9501	1	1	SALAMON	SA_3214	150	7400000
SA9501	1	1	SALAMON	SA_6547	165	6500000
SA9502	1	2	SALAMON	SA_3698	210	5600000
SA9502	2	4	SALAMON	SA_2587	66	5800000
SA9502	2	4	SALAMON	SA_9632	50	6900000

I need total breakdown of order number ( Ord_No ) and invoice number ( Inv_No ) ???

In a expression to this form of writing :

sum ( Amount ) / sum ( TOTAL< Ord_No , Inv_No > Amount )

Please advise me what code to write the script as new variable ???

Thank you

[vinieme12]

what do you mean by breakdown?

Can you explain a sample output

[majid_ghaboli]

Hi

sorry I mean ... I need the total amount per (Ord_No) and (Inv_No) in script

What solution can you suggest

[Anonymous · Accepted solution]

Hi Majid,

Hope below script meets your requirement

Inv_Table_Temp:

Load Ord_No,

     Inv_No,

     Groupe_Code,

     Brand_Code,

     Good_Code,

     Quantity,

     Amount

From ...;

join (Inv_Table_Temp)

Load Ord_No,

     Inv_No,

     sum(Amount) as Amount_Per_Inv

Resident Inv_Table_Temp

group by Ord_No,Inv_No;

Inv_Table:

Load *, Amount/Amount_Per_Inv as Avg_Amt

Resident Inv_Table_Temp;

Drop Table Inv_Table_Temp;