topic Re: Autogenerate dates for each table row in QlikView

Autogenerate dates for each table row

benjamins — Mon, 07 Mar 2016 11:55:09 GMT

I have a table A with an Id and two date columns which describe each element's availabililty (begin and end):

Example:

Id	Begin	End
1	2016-01-01	2016-01-09
2	2016-01-01	2016-01-15
3	2016-01-11	2016-01-17

So the first element would be available from 1st of January to 9th of January and so on.

I'd like to create a table B from this information which contains all dates on which each element would be available.

So this table should have two columns "Id" and "ElementDateId" and contain 31 records total (9 for element 1, 15 for 2 and 7 for 3).

I'd have suspected something like the following (please also see the attached sample), however this doesn't seem to work out:

QV_A:

LOAD

, date(Begin) as BeginDate

, date(End) as EndDate

Resident QV_TEMP_A;

Drop Table QV_TEMP_A;

QV_GENERATE_A:

LOAD ID,

floor(EndDate) - floor(BeginDate) + 1 as num_days,

floor(BeginDate) as floor_begin

Resident QV_A;

For vValueNo = 1 to num_days

LOAD

floor_begin + $(vValueNo) -1 as ElementDateId

AUTOGENERATE num_days;

next vValueNo;

Any ideas?

Thanks a lot

Benjamin

Re: Autogenerate dates for each table row

petter — Mon, 07 Mar 2016 12:26:11 GMT

did you look at this? How to generate missing dates

Re: Autogenerate dates for each table row

petter — Mon, 07 Mar 2016 12:33:08 GMT

One of the quickest ways for your particular data set would be:

LOAD
  Id,
  Date(Begin + IterNo() - 1) AS Date
INLINE [
Id Begin End
1 2016-01-01 2016-01-09
2 2016-01-01 2016-01-15
3 2016-01-11 2016-01-17
] ( delimiter is \t)
WHILE
  IterNo() <= Num(End) - Num(Begin) +1;

If read from an external data source it would be:

LOAD
  Id,
  Date(Begin + IterNo() - 1) AS Date
WHILE
  IterNo() <= Num(End) - Num(Begin) +1;
SQL
     SELECT Id, Begin, End FROM myTable;

Re: Autogenerate dates for each table row

benjamins — Mon, 07 Mar 2016 12:33:35 GMT

If I understand that one correctly at first glance then it is about generating those dates once globally. I need to do this per table row which doesn't work out for me using a cursor.

Re: Autogenerate dates for each table row

benjamins — Mon, 07 Mar 2016 12:50:51 GMT

Perfect!

Works like a charm.

Thanks!