Iterate over date table to autogenerate missing dates with values associated

[#1]

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?

[Nicole-Smith]

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

[#3]

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

[sunny_talwar]

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,

  A,

  B

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

LOAD *,

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

Resident Table

Order By Date desc;

[#5]

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

[#6]

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

[sunny_talwar · Accepted solution]

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,

  A,

  B

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;

[#8]

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

[#9]

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.

[#10]

I forgot to put $(...):

Year($(vMaxDate))