Hi guys,
I have a new Question.
Currently I am mapping a leaseplan in Qlikview
for this i have this type of table:
| Lincense plate | Start date | End date | First month | Month costs |
| AABBCC | 1-1-2014 | 1-3-2014 | 1532 | 1600 |
| DDEEFF | 1-1-2014 | 1-4-2014 | 1234 | 4567 |
| GGGGGG | 1-3-2014 | 1-5-2014 | 1155 | 2434 |
how it should look in Qlikview:
| License plate | Date | End date | Monthly costs | First month | Month costs |
| AABBCC | 1-1-2014 | 1-3-2014 | 1532 | 1532 | 1600 |
| DDEEFF | 1-1-2014 | 1-4-2014 | 1234 | 1234 | 4567 |
| AABBCC | 1-2-2014 | 1-3-2014 | 1600 | 1532 | 1600 |
| DDEEFF | 1-2-2012 | 1-4-2014 | 4567 | 1234 | 4567 |
| AABBCC | 1-3-2014 | 1-3-2014 | 1600 | 1532 | 1600 |
| DDEEFF | 1-3-2014 | 1-4-2014 | 4567 | 1234 | 4567 |
| GGGGGG | 1-3-2014 | 1-5-2014 | 1155 | 1155 | 2434 |
| DDEEFF | 1-4-2014 | 1-4-2014 | 4567 | 1234 | 4567 |
| GGGGGG | 1-4-2014 | 1-5-2014 | 2434 | 1155 | 2434 |
| GGGGGG | 1-5-2014 | 1-5-2014 | 2434 | 1155 | 2434 |
I am trying to fill the series using a MasterCalendar component that just ranges the dates from startdate to enddate, so nothing special.
The problem is only that I cant figure out how to create the right script that fills the dates correctly.
The script code i am using:
Store MasterCalendar into MC.qvd (qvd);
Drop Table MasterCalendar;
Temp1:
LOAD [Lincense plate],
[Start date],
[End date],
[First month],
[Month costs]
FROM
Testing.xlsx
(ooxml, embedded labels, table is Sheet1);
Temp2:
LOAD [Lincense plate],
[Start date] as Date,
[End date],
[First month],
[Month costs]
Resident Temp1;
Join Temp2:
LOAD [Lincense plate],
[End date] as Date
Resident Temp1;
Drop table Temp1;
Join Temp2:
LOAD Date
FROM
MC.qvd
(qvd);
Final:
Noconcatenate Load Distinct
Date,
if(isnull([Lincense plate]),peek([Lincense plate]),[Lincense plate]) as [Lincense plate],
[End date],
[First month],
[Month costs]
Resident Temp2
Where Date<>''
Order By Date ;
Drop Table Temp2;
The overview i am currently getting is:
| Date | Lincense plate |
| 1-1-2014 | AABBCC |
| 1-1-2014 | DDEEFF |
| 2-1-2014 | DDEEFF |
| 3-1-2014 | DDEEFF |
| 4-1-2014 | DDEEFF |
| 5-1-2014 | DDEEFF |
| 6-1-2014 | DDEEFF |
| 7-1-2014 | DDEEFF |
| 8-1-2014 | DDEEFF |
| 9-1-2014 | DDEEFF |
| 10-1-2014 | (and so on) |
You can see my problem in the attached file, the range is not correctly made. Instead of linking multiple LicensePlates to 1 date on the range Start date Enddate, it just puts the last LicensePlate in the NULL values for Date..
Can you please help me out?
Thanks a lot!
Best regards,
Dick
