Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
durgabhavani
Creator III
Creator 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

8 Replies
Gysbert_Wassenaar

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
petter
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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.

petter
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

sorry for late response. I have sorted the problem.

petter
Partner - Champion III
Partner - Champion III

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

durgabhavani
Creator III
Creator III
Author

Petter,

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