Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm not entirely sure how to best describe this (but see below tables which should hopefully illustrate this) but I am wanting to group data, in the script, by a docketID and then assign a particular code to that particular docketID but not the code N/R. So for CustomerA with a code of R199 I want to group the assigned docket ID by summing the totals of (19,000, 45, 60, 125, 400 and 2000) and then assign the code of R199 to all of this data.
Thanks,
Ralph
Original dataset:
Customer | Date | Code | Total | DocketID |
---|---|---|---|---|
CustomerA | 16/07/2013 | R199 | 19000 | 12345 |
CustomerA | 16/07/2013 | N/R | 45 | 12345 |
CustomerA | 16/07/2013 | N/R | 60 | 12345 |
CustomerA | 16/07/2013 | N/R | 125 | 12345 |
CustomerA | 16/07/2013 | N/R | 400 | 12345 |
CustomerA | 16/07/2013 | N/R | 2000 | 12345 |
CustomerA | 16/07/2013 | R285 | 50000 | 45678 |
CustomerA | 16/07/2013 | N/R | 124 | 45678 |
CustomerA | 16/07/2013 | N/R | 560 | 45678 |
CustomerA | 16/07/2013 | N/R | 2000 | 45678 |
CustomerB | 16/07/2013 | R199 | 25000 | 56789 |
CustomerB | 16/07/2013 | R285 | 35000 | 67890 |
CustomerB | 1707/2013 | R670 | 18500 | 78901 |
Required data:
Customer | Date | Code | Total | DocketID |
---|---|---|---|---|
CustomerA | 16/07/2013 | R199 | 21630 | 12345 |
CustomerA | 16/07/2013 | R285 | 52684 | 45678 |
CustomerB | 16/07/2013 | R199 | 25000 | 56789 |
CustomerB | 16/07/2013 | R285 | 35000 | 67890 |
CustomerB | 1707/2013 | R670 | 18500 | 78901 |
See attached qvw.
Thanks for that. Ideally I would like to have it done in the script as I need to link a table which has targets for the particular codes, e.g. code R199 would have a target of 24000.
Thanks,
Ralph
Something like this will work if your source table is already sorted as your posted example:
load Customer, Date, Code, Total, DocketID,
if(Code='N/R',peek('NewCode',Code) as NewCode
from ...somewhere...;
drop field Code;
rename field NewCode to Code;
Hi Ralph,
please see the example for help.
Good luck!
Rainer