topic Re: Iterate over date table to autogenerate missing dates with values associated in QlikView

Iterate over date table to autogenerate missing dates with values associated

Wed, 25 Nov 2020 16:16:04 GMT

I have a table that I have loaded from an excel file. This table contains quarters for each year:

Date A B

01/01/2009 34 100

01/04/2009 12 80

01/07/2009 58 20

01/10/2009 24 10

01/01/2010 56 23

01/04/2010 14 40

01/07/2010 9 79

01/10/2010 20 120

and I want to obtain below table:

Date A B

01/01/2009 34 100

02/01/2009 34 100

03/01/2009 34 100

04/01/2009 34 100

....

01/02/2009 34 100

02/02/2009 34 100

03/02/2009 34 100

...

01/04/2009 12 80

02/04/2009 12 80

03/04/2009 12 80

...

01/06/2009 12 80

02/06/2009 12 80

...

01/07/2009 58 20

02/07/2009 58 20

01/09/2009 58 20

02/09/2009 58 20

...

01/10/2009 24 10

02/10/2009 24 10

03/10/2009 24 10

04/10/2009 24 10

...

01/11/2009 24 10

...

01/12/2009 24 10

01/01/2010 64 30

02/01/2010 64 30

Note that each time a quarter for a year starts I need to autogenerate next dates and repeat values for columns A and B until to reach next quarter. Once next quarter is reached I should begin to repeat new values for columns A and B until next one, and so on.

How to do it in QlikView?

Re: Iterate over date table to autogenerate missing dates with values associated

Nicole-Smith — Fri, 29 Jul 2016 14:52:23 GMT

This post (How to populate a sparsely populated field) by hic‌ should have what you need.

Re: Iterate over date table to autogenerate missing dates with values associated

Sat, 30 Jul 2016 19:02:30 GMT

Hello Tony,

As it is very easy in this situation to calculate end Date of each period, I went with IntervalMatch here. Try attached app.

BR,

Kuba

Re: Iterate over date table to autogenerate missing dates with values associated

sunny_talwar — Sat, 30 Jul 2016 19:24:31 GMT

Another alternative:

Table:

LOAD * INLINE [

Date, A, B

01/01/2009, 34, 100

01/04/2009, 12, 80

01/07/2009, 58, 20

01/10/2009, 24, 10

01/01/2010, 56, 23

01/04/2010, 14, 40

01/07/2010, 9, 79

01/10/2010, 20, 120

];

FinalTable:

NoConcatenate

LOAD Date(Date + IterNo() - 1) as Date,

While Date + IterNo() - 1 <= EndDate;

LOAD *,

Date(Alt(Previous(Date), Today()) - 1) as EndDate

Resident Table

Order By Date desc;

Re: Iterate over date table to autogenerate missing dates with values associated

Sun, 31 Jul 2016 20:30:08 GMT

I like your solution, it's very simple but instead of generating dates to today I would like to generate dates to 31/12/2010. How can I do this?

it would be:

01/10/2010, 20, 120

02/10/2010, 20, 120

03/10/2010, 20, 120

...

31/12/2010, 20, 120

Re: Iterate over date table to autogenerate missing dates with values associated

Sun, 31 Jul 2016 20:32:53 GMT

Your solution is great but I prefer to not use a calendar.

Re: Iterate over date table to autogenerate missing dates with values associated

sunny_talwar — Sun, 31 Jul 2016 20:33:46 GMT

All you need to do is change the part in the red:

Table:

LOAD * INLINE [

Date, A, B

01/01/2009, 34, 100

01/04/2009, 12, 80

01/07/2009, 58, 20

01/10/2009, 24, 10

01/01/2010, 56, 23

01/04/2010, 14, 40

01/07/2010, 9, 79

01/10/2010, 20, 120

];

FinalTable:

NoConcatenate

LOAD Date(Date + IterNo() - 1) as Date,

While Date + IterNo() - 1 <= EndDate;

LOAD *,

Date(Alt((Previous(Date) - 1), MakeDate(2010, 12, 31))) as EndDate

Resident Table

Order By Date desc;

DROP Table Table;

Re: Iterate over date table to autogenerate missing dates with values associated

Sun, 31 Jul 2016 20:38:57 GMT

Hey Tony,

It is needed only to generate rows. You can safely drop Calendar table as a last step in the load script.

(DROP TABLE Calendar;)

BR,

Kuba

Re: Iterate over date table to autogenerate missing dates with values associated

Sun, 31 Jul 2016 21:27:42 GMT

Instead of fixing the date to fix the year to 2010 I would like to get the maximum date and obtain the year from there. So I do the following:

Temp:

Load Max(Date) as MaxDate

Resident [MyTable_of_dates];

Let vMaxDate = FieldValue('MaxDate',1);

Drop Table Temp;

Then in load I try to do:

LOAD *,

Date(Alt((Previous(Date) - 1), MakeDate(Year(vMaxDate), 12, 31))) as EndDate

Resident Table

But an error occurs, it says "vMaxDate" field not found.

Re: Iterate over date table to autogenerate missing dates with values associated

Sun, 31 Jul 2016 21:45:44 GMT

I forgot to put $(...):

Year($(vMaxDate))

Re: Iterate over date table to autogenerate missing dates with values associated

sunny_talwar — Sun, 31 Jul 2016 22:11:44 GMT

So it is all working now?

Re: Iterate over date table to autogenerate missing dates with values associated

Mon, 01 Aug 2016 18:46:13 GMT

Yes, all work now. Many thanks