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..
