How to expand dates with values.

[#1]

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.

[swuehl]

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

[#3]

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

[johnw]

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;

[johnw · Accepted solution]

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
;

[swuehl]

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

[johnw]

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.

[johnw]

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
;

[rwunderlich]

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