Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
This post (How to populate a sparsely populated field) by hic should have what you need.
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
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;
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
Your solution is great but I prefer to not use a calendar.
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;
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
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.
I forgot to put $(...):
Year($(vMaxDate))