URGENT: Fill dates with multiple values

dickelsa — Fri, 10 Oct 2014 10:39:23 GMT

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

Re: URGENT: Fill dates with multiple values

Gysbert_Wassenaar — Fri, 10 Oct 2014 11:38:52 GMT

See attached qvw.

And read this blog post: Creating Reference Dates for Intervals

Re: URGENT: Fill dates with multiple values

dickelsa — Fri, 10 Oct 2014 13:05:44 GMT

Thanks!

topic Re: URGENT: Fill dates with multiple values in QlikView

URGENT: Fill dates with multiple values

Re: URGENT: Fill dates with multiple values

Re: URGENT: Fill dates with multiple values