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

[durgabhavani]

Hi All,

Please help me to derive new field iteration number based on below scenario. I am assuming Peek function needs to be used to derive the field. Please find the scenario in detail and expected output.

1. Group iteration number needs to start assign number from first time category "CAP" and Group GS.(here find example highlighted 1)

2. Group iteration needs to increase number when it changes from category "CAP" to "CAP and from Group BUS to GS or "CAP" to "QC" and from Group BUS to GS. (here find example highlighted 2).

3. Finally Group iteration needs to change until the category changed to "QC" and from Group BUS to GS. (here find example highlighted 3). Here after no need to change the iteration number irrespective of change in category or group.

Please let me know if any questions. Thanks in advance.

Sample data and expected output column:

ID	Category	Group	Group_iteration (Expected Output)
101	CAP	FCR	0
101	CAP	GS	1
101	CAP	BUS	1
101	CAP	BUS	1
101	CAP	BUS	1
101	CAP	BUS	1
101	CAP	GS	2
101	CAP	GS	2
101	CAP	GS	2
101	CAP	BUS	2
101	CAP	BUS	2
101	QC	GS	3
101	QC	GS	3
101	CAP	BUS	3
101	CAP	GS	3
101	APP	BUS	3
101	CAP	GS	3
101	QC	GS	3
101	CAP	GS	3
101	QC	GS	3
101	APP	BUS	3
101	CAP	GS	3

Thanks,

Durga

[petter]

I think this load script might work for you:

DATA:
LOAD
  RecNo() AS R#,
  ID,
  Category,
  Group,
  If(ID<>Peek('ID')
    ,0
    ,If(Group='GS' AND Peek('Group')<>Group AND Peek('FI')=0, Peek('GI')+1, Peek('GI'))
  ) AS GI,  // Group_iteration
  If(ID<>Peek('ID')
    ,0
    ,If(Category='QC',1,Peek('FI'))
  ) AS FI  // Finished Indicator
INLINE [
ID,Category,Group,GI
101,CAP,FCR,0
101,CAP,GS,1
101,CAP,BUS,1
101,CAP,BUS,1
101,CAP,BUS,1
101,CAP,BUS,1
101,CAP,GS,2
101,CAP,GS,2
101,CAP,GS,2
101,CAP,BUS,2
101,CAP,BUS,2
101,QC,GS,3
101,QC,GS,3
101,CAP,BUS,3
101,CAP,GS,3
101,APP,BUS,3
101,CAP,GS,3
101,QC,GS,3
101,CAP,GS,3
101,QC,GS,3
101,APP,BUS,3
101,CAP,GS,3
];

DROP FIELDS R#,FI;
RENAME FIELD GI TO Group_iteration;

[durgabhavani]

Thanks for reply Petter. I will try and let you know.

[petter]

The code actually works I tested it so I hope it is easy for you to adjust to your needs.

[petter]

Please mark the question as answered when you get a response that is actually answering exactly what you asked about. If there are details you need clarified please ask about them too.

You can respond with both HELP and ANSWERED when tagging any anwser.

[durgabhavani]

Petter.

This is very helpful solution and able to progress the output for some extent. i am facing problem with my existing code to solve current problem. Can you please answer the latest post having sample application along with my existing code.

Please advice. Thanks,Durga

[petter]

I will. Please close this thread and mark it as answered.