Group by and then assign a particular value

[#1]

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

[Gysbert_Wassenaar]

See attached qvw.

[#3]

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

[Gysbert_Wassenaar]

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;

[#5]

Hi Ralph,

please see the example for help.

Good luck!

Rainer