Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Group by and then assign a particular value

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:

CustomerDateCodeTotalDocketID
CustomerA16/07/2013R1991900012345
CustomerA16/07/2013N/R4512345
CustomerA16/07/2013N/R6012345
CustomerA16/07/2013N/R12512345
CustomerA16/07/2013N/R40012345
CustomerA16/07/2013N/R200012345
CustomerA16/07/2013R2855000045678
CustomerA16/07/2013N/R12445678
CustomerA16/07/2013N/R56045678
CustomerA16/07/2013N/R200045678

CustomerB

16/07/2013R1992500056789
CustomerB16/07/2013R2853500067890
CustomerB1707/2013R6701850078901

Required data:

CustomerDateCodeTotalDocketID
CustomerA16/07/2013R1992163012345
CustomerA16/07/2013R2855268445678
CustomerB16/07/2013R1992500056789
CustomerB16/07/2013R2853500067890
CustomerB1707/2013R6701850078901
Tags (2)
4 Replies
Gysbert_Wassenaar
Not applicable

Re: Group by and then assign a particular value

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: Group by and then assign a particular value

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
Not applicable

Re: Group by and then assign a particular value

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Group by and then assign a particular value

Hi Ralph,

please see the example for help.

Good luck!

Rainer