topic How to expand dates with values. in QlikView

How to expand dates with values.

Sat, 17 Dec 2011 00:17:50 GMT

How do I expand a list of span days with values?

For example

11/01/2011, 1

11/05/2011, 5

11/12/2011, 10

11/20/2011, 20

11/30/2011, 3

should expand into these values

11/01/2011, 1

11/02/2011, 1

11/03/2011, 1

11/04/2011, 1

11/05/2011, 5

11/06/2011, 5

11/07/2011, 5

11/08/2011, 5

11/09/2011, 5

11/10/2011, 5

11/11/2011, 5

11/12/2011, 10

11/13/2011, 10

11/14/2011, 10

11/15/2011, 10

11/16/2011, 10

11/17/2011, 10

11/18/2011, 10

11/19/2011, 10

11/20/2011, 20

11/21/2011, 20

11/22/2011, 20

11/23/2011, 20

11/24/2011, 20

11/25/2011, 20

11/26/2011, 20

11/27/2011, 20

11/28/2011, 20

11/29/2011, 20

Attached is the QVW.

How to expand dates with values.

swuehl — Sat, 17 Dec 2011 00:22:42 GMT

This looks similar to what I answered just a few hours ago, maybe it is helpful also to you:

http://community.qlik.com/thread/40903

You would need to create the range of dates you want to fill in step 2 and in step, do a asc sort instead a descending.

Regards,

Stefan

Re: How to expand dates with values.

Sat, 17 Dec 2011 00:26:32 GMT

Do you have an example in a QVW? I don’t have the technical experience to resolve

Re: How to expand dates with values.

johnw — Sat, 17 Dec 2011 00:33:25 GMT

Raw:

LOAD * INLINE [

Date, Value

11/01/2011, 1

11/05/2011, 5

11/12/2011, 10

11/20/2011, 20

11/30/2011, 3

];

LEFT JOIN (Raw)

LOAD *

,previous(Date) as ToDate

// Use the below to use the final row and expand it out to yesterday

//,date(if(previous(Date),previous(Date),today())) as ToDate

RESIDENT Raw

ORDER BY Date DESC

;

Table:

LOAD

date(Date+iterno()-1) as Date

,Value

RESIDENT Raw

WHILE Date+iterno()-1 < ToDate

;

DROP TABLE Raw;

Re: How to expand dates with values.

johnw — Sat, 17 Dec 2011 00:45:12 GMT

Stefan's method adapted to your case. Hopefully this is what he meant:

Raw:
LOAD * INLINE [
Date, Value
11/01/2011, 1
11/05/2011, 5
11/12/2011, 10
11/20/2011, 20
11/30/2011, 3
];
Range:
LOAD
min(Date) as MinDate
,max(Date) as MaxDate
RESIDENT Raw
;
LET vMinDate = peek('MinDate');
LET vMaxDate = peek('MaxDate');

RIGHT JOIN (Raw)
LOAD date($(vMinDate)+recno()-1) as Date
AUTOGENERATE $(vMaxDate) - $(vMinDate)
;
Table:
NOCONCATENATE
LOAD
Date
,if(Value,Value,peek(Value)) as Value
RESIDENT Raw
WHERE Date < $(vMaxDate)
ORDER BY Date
;
DROP TABLES
Raw
,Range
;

How to expand dates with values.

swuehl — Sat, 17 Dec 2011 01:01:24 GMT

This time you you were much faster than me John, I would say twice as fast 😉

Yes, that's pretty much what I had in mind.

Hm, if spb wants the value of 11/30/2011 to be included, I believe our both approaches as written here will miss this date. Probably just a +1 in the autogenerate and

,date(if(previous(Date),previous(Date),Date)) as ToDate

should be enough to correct both, I think.

Both, have a nice weekend,

Stefan

How to expand dates with values.

johnw — Sat, 17 Dec 2011 01:11:27 GMT

That's almost exactly the solution I proposed in the comment in the code in the first example, but I made the assumption we'd want to fill the dates up through yesterday rather than just including that one date. Same idea, though.

Re: How to expand dates with values.

johnw — Sat, 17 Dec 2011 01:14:23 GMT

The way I would expect most people to solve this is with intervalmatch. I don't recommend it, as I think it's the most complicated solution of the three. But here it is for reference.

Raw:
LOAD
Date as FromDate
,Value
INLINE [
Date, Value
11/01/2011, 1
11/05/2011, 5
11/12/2011, 10
11/20/2011, 20
11/30/2011, 3
];
LEFT JOIN (Raw)
LOAD *
,previous(FromDate)-1 as ToDate
RESIDENT Raw
ORDER BY FromDate DESC
;
Range:
LOAD
min(FromDate) as MinDate
,max(FromDate) as MaxDate
RESIDENT Raw
;
LET vMinDate = peek('MinDate');
LET vMaxDate = peek('MaxDate');

Dates:
LOAD date($(vMinDate)+recno()-1) as Date
AUTOGENERATE $(vMaxDate) - $(vMinDate)
;
INNER JOIN (Raw)
INTERVALMATCH (Date)
LOAD FromDate, ToDate
RESIDENT Raw
;
DROP TABLES
Range
,Dates
;
DROP FIELDS
FromDate
,ToDate
;

Re: How to expand dates with values.

rwunderlich — Sat, 17 Dec 2011 20:23:11 GMT

If the input table is already sorted, then here's another suggestion.

Raw:

LOAD * INLINE [

Date, Value

11/01/2011, 1

11/05/2011, 5

11/12/2011, 10

11/20/2011, 20

11/30/2011, 3

];

JOIN (Raw)

LOAD

Date(Date + IterNo()) as Date,

Value

RESIDENT Raw

WHILE (Date + IterNo()) < peek('Date', RecNo(), 'Raw')

;

-Rob