Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
I have a table as below:
LOAD [SALES ID],
[Paid Cash],
[Paid Card],
[On Account],
[Paid Cheque],
[Paid In Account]
Resident Temp2;
I need to create a calculated field in there which does Sum([Paid Cash] + [Paid Card] + [On Account] + [Paid Cheque] +[Paid In Account]) as [Total Paid]
Can anyone suggest how to do this?
Thanks,
HI,
Try like this using Rangesum() like below,
LOAD [SALES ID],
[Paid Cash],
[Paid Card],
[On Account],
[Paid Cheque],
[Paid In Account],
Rangesum([Paid Cash], [Paid Card] , [On Account] , [Paid Cheque] , [Paid In Account]) as [Total Paid]
Resident Temp2;
Hope this helps you.
Regards,
jagan.
Maybe,
TMP:
LOAD [SALES ID],
[Paid Cash],
[Paid Card],
[On Account],
[Paid Cheque],
[Paid In Account]
Resident Temp2;
Sales:
Load
[SALES ID]
Sum([Paid Cash] + [Paid Card] + [On Account] + [Paid Cheque] +[Paid In Account]) as [Total Paid]
Resident TMP
Group by [SALES ID];
regards
The solution by Jannet is probably the best you can get, because there is no other discerning field for identifying individual records.
If on the other hand, you want to keep just the sum of the individual fields (in QlikView terms, there is no difference at all between the two solutions if you aggregate by [SALES ID] except when processing huge numbers os rows = script execution takes longer, UI responses come quicker), you can just throw the various fields together row-by-row, like in:
LOAD [SALES ID],
[Paid Cash],
[Paid Card],
[On Account],
[Paid Cheque],
[Paid In Account],
[Paid Cash] + [Paid Card] + [On Account] + [Paid Cheque] +[Paid In Account]) as [Total Paid]
Resident Temp2;
Sum([Total Paid]) will produce the same result in both cases.
Best,
Peter
PS Use RangeSum() if you want to avoid getting a NULL value as total if any single field contains Null.
HI,
Try like this using Rangesum() like below,
LOAD [SALES ID],
[Paid Cash],
[Paid Card],
[On Account],
[Paid Cheque],
[Paid In Account],
Rangesum([Paid Cash], [Paid Card] , [On Account] , [Paid Cheque] , [Paid In Account]) as [Total Paid]
Resident Temp2;
Hope this helps you.
Regards,
jagan.
Everyone thanks for your help.
Jagan this works fine.
Regards,
Hi,
If you got the answer please close this thread by giving Correct Answer to the post which helps you in getting the answer..
Regards,
Jagan.