Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

majid_ghaboli
New Contributor

Total amount in terms of specific variables in the script

Hi

please guide me

I have table similar below :

Ord_NoInv_NoGroupe_CodeBrand_CodeGood_CodeQuantityAmount
AT950111ATOMICAT_9501501000000
AT950111ATOMICAT_95061002130000
AT950122ATOMICAT_95881202500000
NK950113NIKENK_32882003200000
NK950113NIKENK_65281805200000
NK950124NIKENK_14782004100000
SA950111SALAMONSA_32141507400000
SA950111SALAMONSA_65471656500000
SA950212SALAMONSA_36982105600000
SA950224SALAMONSA_2587665800000
SA950224SALAMONSA_9632506900000

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

Tags (1)
1 Solution

Accepted Solutions
payalgosar
Contributor II

Re: Total amount in terms of specific variables in the script

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;

3 Replies
vinieme12
Esteemed Contributor II

Re: Total amount in terms of specific variables in the script

what do you mean by breakdown?

Can you explain a sample output

majid_ghaboli
New Contributor

Re: Total amount in terms of specific variables in the script

Hi

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

What solution can you suggest

payalgosar
Contributor II

Re: Total amount in terms of specific variables in the script

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;

Community Browser