Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My input is like:
PDI ID | STAGES | DATE |
---|---|---|
PDI1 | 10 | 1/2/2016 |
PDI1 | 20 | 3/2/2016 |
PDI1 | 30 | 4/2/2016 |
PDI2 | 10 | 1/3/2016 |
PDI2 | 20 | 3/3/2016 |
Now,For each PDI i wanted to calculate the difference of each stages and store it in separate column.The output should be as follows:
Here age-10 is calculated by finding the difference between the dates of stage-10 and stage 20 and so on
PDI ID | AGE-10 | AGE-20 | AGE-30 | DATE-10 | DATE-20 | DATE-30 |
---|---|---|---|---|---|---|
PDI1 | 1 | 2 | 0 | 1/2/2016 | 3/2/2016 | 4/2/2016 |
PDI2 | 2 | 0 | 0 | 1/3/2016 | 3/3/2016 | - |
can anyone help me with the logic and script for this scenario?
please explain how the difference between 1/2/2016 and 3/2/2016 is 1 while the difference between 3/2/2016 and 4/2/2016 is 2 ?
thanks
regards
Marco
sorry that was wrong data
age-10 should be 2 and age-20 should be 1
try this
In strtaight table
for Age-10 :
Count({<Stage ={'10'}>} Stage)
age-20
Count({<Stage ={'20'}>} Stage)
age-30
Count({<Stage ={'30'}>} Stage)
Date-10
=Only({<Stage ={'10'}>}Date)
Date-20
=Only({<Stage ={'20'}>}Date)
Date-30
=Only({<Stage ={'30'}>}Date)
PDIID | Age-10 | Age-20 | Age-30 | Date-10 | Date-20 | Date-30 |
2 | 2 | 1 | - | - | 4/2/2016 | |
PDI1 | 1 | 1 | 1 | 01/02/2016 | 3/2/2016 | 4/2/2016 |
PDI2 | 1 | 1 | 0 | 01/03/2016 | 3/3/2016 | - |
Hi,
maybe like this?
tabTemp:
LOAD * FROM [https://community.qlik.com/thread/247061] (html, codepage is 1252, embedded labels, table is @1);
table1:
Generic
LOAD [PDI ID],
'DATE-'&STAGES,
DATE
Resident tabTemp;
table1:
Generic
LOAD [PDI ID],
'AGE-'&STAGES,
Alt(If(Previous([PDI ID])=[PDI ID],Previous(DATE)-DATE),0)
Resident tabTemp
Order By [PDI ID], DATE desc;
DROP Table tabTemp;
hope this helps
regards
Marco
Generic load concept was very useful thankyou
you're welcome
glad it worked for you
please close your thread if your question is answered:
Qlik Community Tip: Marking Replies as Correct or Helpful
thanks
regards
Marco