topic Re: How to get date between to values in QlikView

How to get date between to values

Wed, 10 Aug 2016 11:59:35 GMT

Hi guys!

I have a person that has colors and signed dates. Is there somehow i could isolate the dates between the colors. Like i need the signed dates for the colors red so the the dates should be so if the color is red it should get the the date after .

Example how it should look based on the data in the end

2014-05-08 to 2014-11-05

2015-05-12 to 2016-06-22

2016-06-22 to 2016-07-15

2016-08-01 to 2016-08-09

And if thats the last signed date it should stop.

Any ideas om stuck

Person	Signed	PGValue	Color
Alfred	2014-05-08	8	Red
Alfred	2014-11-05	5	Yellow
Alfred	2015-05-12	5	Red
Alfred	2016-06-22	5	Red
Alfred	2016-07-15	8	Grön
Alfred	2016-07-20	5	Gul
Alfred	2016-08-01	5	Red
Alfred	2016-08-09	5	Red

Re: How to get date between to values

avinashelite — Wed, 10 Aug 2016 12:03:52 GMT

Did not get your requirement could you please elaborate bit more on that ..with sample data and the desired output you required

Re: How to get date between to values

Wed, 10 Aug 2016 12:07:57 GMT

Output these dates! Based on the data below, so the date from Red color to the next color

2014-05-08 to 2014-11-05

2015-05-12 to 2016-06-22

2016-06-22 to 2016-07-15

2016-08-01 to 2016-08-09

Data

Person	Signed	PGValue	Color
Alfred	2014-05-08	8	Red
Alfred	2014-11-05	5	Yellow
Alfred	2015-05-12	5	Red
Alfred	2016-06-22	5	Red
Alfred	2016-07-15	8	Grön
Alfred	2016-07-20	5	Gul
Alfred	2016-08-01	5	Red
Alfred	2016-08-09	5	Red

Re: How to get date between to values

Wed, 10 Aug 2016 12:10:08 GMT

The signed date i mean from every red color to the signed date of next color

Re: How to get date between to values

Gysbert_Wassenaar — Wed, 10 Aug 2016 12:20:28 GMT

Perhaps like this:

Data:

LOAD

Person,

Date(Date#(Signed,'YYYY-MM-DD'),'YYYY-MM-DD') as Signed,

PGValue,

Color

INLINE [

Person, Signed, PGValue, Color

Alfred, 2014-05-08, 8, Red

Alfred, 2014-11-05, 5, Yellow

Alfred, 2015-05-12, 5, Red

Alfred, 2016-06-22, 5, Red

Alfred, 2016-07-15, 8, Grön

Alfred, 2016-07-20, 5, Gul

Alfred, 2016-08-01, 5, Red

Alfred, 2016-08-09, 5, Red

];

Result:

LOAD * WHERE Start;

LOAD

Person, Signed as End,

If(Person=Previous(Person) AND Previous(Color)='Red',Previous(Signed)) as Start

RESIDENT

Data

ORDER BY

Person, Signed

;

Re: How to get date between to values

Wed, 10 Aug 2016 12:28:43 GMT

Sorry did not get the desired outcome.

Re: How to get date between to values

Gysbert_Wassenaar — Wed, 10 Aug 2016 12:33:48 GMT

Fine. Try this then:

Data:

LOAD

Person,

Date(Date#(Signed,'YYYY-MM-DD'),'YYYY-MM-DD') as Signed,

PGValue,

Color

INLINE [

Person, Signed, PGValue, Color

Alfred, 2014-05-08, 8, Red

Alfred, 2014-11-05, 5, Yellow

Alfred, 2015-05-12, 5, Red

Alfred, 2016-06-22, 5, Red

Alfred, 2016-07-15, 8, Grön

Alfred, 2016-07-20, 5, Gul

Alfred, 2016-08-01, 5, Red

Alfred, 2016-08-09, 5, Red

];

Result:

LOAD *, Start & ' to ' & End as Interval WHERE Start;

LOAD

Person, Signed as End,

If(Person=Previous(Person) AND Previous(Color)='Red',Previous(Signed)) as Start

RESIDENT

Data

ORDER BY

Person, Signed

;

Re: How to get date between to values

Wed, 10 Aug 2016 12:38:55 GMT

Ok how would the the code look if the data was loaded from SQL?

Re: How to get date between to values

Gysbert_Wassenaar — Wed, 10 Aug 2016 12:50:19 GMT

Depends. You could replace the INLINE load with an sql statement. If you want to do all the calculations in SQL then ask your local friendly database administrator/developer

Re: How to get date between to values

Wed, 10 Aug 2016 12:52:20 GMT

I looked for a qick fix thanks, il manage to solve it. The data i already there with all the calculations. Thank for your help