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

[durgabhavani]

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:

ID	CATEGORY	GROUP	DATE
101	CAP	FCR	23-Nov-2015
101	CAP	GS	24-Nov-2015
101	CAP	BUS	30-Nov-2015
101	CAP	GS	01-Dec-2015
101	CAP	BUS	02-Dec-2015
101	CAP	GS	02-Dec-2015
101	CAP	BUS	07-Dec-2015
101	CAP	GS	07-Dec-2015
101	QC	GS	07-Dec-2015
101	APP	BUS	08-Dec-2015
101	CAP	GS	08-Dec-2015
101	QC	GS	11-Dec-2015
101	CAP	GS	12-Dec-2015
101	QC	GS	12-Dec-2015
101	APP	BUS	14-Dec-2015
101	CAP	GS	14-Dec-2015

Expected Output:

ID	CATEGORY	GROUP	DATE	GROUP_ITERATION
101	CAP	FCR	23-Nov-2015	0
101	CAP	GS	24-Nov-2015	0
101	CAP	BUS	30-Nov-2015	0
101	CAP	GS	01-Dec-2015	0
101	CAP	BUS	02-Dec-2015	0
101	CAP	GS	02-Dec-2015	0
101	CAP	BUS	07-Dec-2015	0
101	CAP	GS	07-Dec-2015	0
101	QC	GS	07-Dec-2015	1
101	APP	BUS	08-Dec-2015	1
101	CAP	GS	08-Dec-2015	2
101	QC	GS	11-Dec-2015	2
101	CAP	GS	12-Dec-2015	3
101	QC	GS	12-Dec-2015	3
101	APP	BUS	14-Dec-2015	3
101	CAP	GS	14-Dec-2015	4

Thanks,

Durga

[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.

[petter]

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]

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]

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]

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]

sorry for late response. I have sorted the problem.

[petter]

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

[durgabhavani]

Petter,

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