topic Re: Logic to find the difference between values in a column in QlikView

Logic to find the difference between values in a column

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Logic to find the difference between values in a column

MarcoWedel — Mon, 23 Jan 2017 22:07:06 GMT

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

Re: Logic to find the difference between values in a column

Tue, 24 Jan 2017 06:46:51 GMT

sorry that was wrong data

age-10 should be 2 and age-20 should be 1

Re: Logic to find the difference between values in a column

Anonymous — Tue, 24 Jan 2017 08:37:56 GMT

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	-

Re: Logic to find the difference between values in a column

MarcoWedel — Fri, 27 Jan 2017 00:00:38 GMT

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

Re: Logic to find the difference between values in a column

Mon, 30 Jan 2017 10:25:52 GMT

Generic load concept was very useful thankyou

Re: Logic to find the difference between values in a column

MarcoWedel — Tue, 31 Jan 2017 00:14:54 GMT

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