Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

durgabhavani
Contributor III

Please help me to modify my existing script to derive new field.

Hi All,

Please help me to modify my existing script to derive new field GROUP_ITERATION_NO. (my sample application attached)

1. Group iteration number needs to start assign number from ID having first time category "QC" and Group GS.(here find example in expected output below having group iteration first time 1)

2. Group iteration needs to increase number when it changes from any category to "CAP" category and from any Group to"GS" (here find example in expected output below).


Please find sample application and existing code in attached sample copy. please modify the code to get expected ouput. Also please find the expected output below. Thanks in advance.


Sample data:


   

IDCATEGORYGROUPDATE
101CAPFCR23-Nov-2015
101CAPGS24-Nov-2015
101CAPBUS30-Nov-2015
101CAPGS01-Dec-2015
101CAPBUS02-Dec-2015
101CAPGS02-Dec-2015
101CAPBUS07-Dec-2015
101CAPGS07-Dec-2015
101QCGS07-Dec-2015
101APPBUS08-Dec-2015
101CAPGS08-Dec-2015
101QCGS11-Dec-2015
101CAPGS12-Dec-2015
101QCGS12-Dec-2015
101APPBUS14-Dec-2015
101CAPGS14-Dec-2015



Expected Output:


   

IDCATEGORYGROUPDATEGROUP_ITERATION
101CAPFCR23-Nov-20150
101CAPGS24-Nov-20150
101CAPBUS30-Nov-20150
101CAPGS01-Dec-20150
101CAPBUS02-Dec-20150
101CAPGS02-Dec-20150
101CAPBUS07-Dec-20150
101CAPGS07-Dec-20150
101QCGS07-Dec-20151
101APPBUS08-Dec-20151
101CAPGS08-Dec-20152
101QCGS11-Dec-20152
101CAPGS12-Dec-20153
101QCGS12-Dec-20153
101APPBUS14-Dec-20153
101CAPGS14-Dec-20154

Thanks,

Durga

Tags (1)
8 Replies
MVP & Luminary
MVP & Luminary

Re: Please help me to modify my existing script to derive new field.

You can use the previous and peek functions for this. See this technical brief: Peek() vs Previous() – When to Use Each. Try it yourself first. You'll want to use peek on the group_iteration field so you can get the old value and increment it when the conditions are right and propagate the old value in the other case. If you can't make it work then feel free to ask for more help.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Please help me to modify my existing script to derive new field.

It is very similar to the challenge you posted a couple of days ago.... which I gave you a validated solution for. It would be easy to extend that code:

Help me to derive new field iteration number based on my scenario?

durgabhavani
Contributor III

Re: Please help me to modify my existing script to derive new field.

Thanks for reply Gysbert. I have already tried Peek and Previous able to solve the problem for some extent but not completed full requirement. Please see my sample application attached what  I have sorted and what is pending. please advice me.

durgabhavani
Contributor III

Re: Please help me to modify my existing script to derive new field.

Thanks for reply Petter.

I have sorted the problem for some extent with your idea. but not able to solve it completely. Can you please find my attached application what I have tried. Also note that my requirement is slightly changed compared to previously posted. (latest one is explained in current post along with expected output) I have tried the latest one but strucked to complete. Please advice.

MVP
MVP

Re: Please help me to modify my existing script to derive new field.

you could used the methods I used in the first advice but instead of testing for a finished condition with a helper field you could test for a starting condition with a helper field.

If you need both you just have two fields one for start condition and one for end condition....

durgabhavani
Contributor III

Re: Please help me to modify my existing script to derive new field.

sorry for late response. I have sorted the problem.

MVP
MVP

Re: Please help me to modify my existing script to derive new field.

Please mark the appropriate answer as correct so the thread can be closed

durgabhavani
Contributor III

Re: Please help me to modify my existing script to derive new field.

Petter,

unfortunately, I am not able to find the action - Correct Answer. for this post. I marked it as Helpful.