Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
durgabhavani
Creator III
Creator III

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

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:

   

IDCategoryGroupGroup_iteration (Expected Output)
101CAPFCR0
101CAPGS1
101CAPBUS1
101CAPBUS1
101CAPBUS1
101CAPBUS1
101CAPGS2
101CAPGS2
101CAPGS2
101CAPBUS2
101CAPBUS2
101QCGS3
101QCGS3
101CAPBUS3
101CAPGS3
101APPBUS3
101CAPGS3
101QCGS3
101CAPGS3
101QCGS3
101APPBUS3
101CAPGS3

Thanks,

Durga

6 Replies
petter
MVP
MVP

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

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

petter
MVP
MVP

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

petter
MVP
MVP

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

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
MVP
MVP

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