Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

dickelsa
Contributor

URGENT: Fill dates with multiple values

Hi guys,

I have a new Question.

Currently I am mapping a leaseplan in Qlikview

for this i have this type of table:

Lincense plateStart dateEnd dateFirst monthMonth costs
AABBCC1-1-20141-3-201415321600
DDEEFF1-1-20141-4-201412344567
GGGGGG1-3-20141-5-201411552434

how it should look in Qlikview:

License plateDateEnd dateMonthly costsFirst monthMonth costs
AABBCC1-1-20141-3-2014153215321600
DDEEFF1-1-20141-4-2014123412344567
AABBCC1-2-20141-3-2014160015321600
DDEEFF1-2-20121-4-2014456712344567
AABBCC1-3-20141-3-2014160015321600
DDEEFF1-3-20141-4-2014456712344567
GGGGGG1-3-20141-5-2014115511552434
DDEEFF1-4-20141-4-2014456712344567
GGGGGG1-4-20141-5-2014243411552434
GGGGGG1-5-20141-5-2014243411552434

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:

DateLincense plate
1-1-2014AABBCC
1-1-2014DDEEFF
2-1-2014DDEEFF
3-1-2014DDEEFF
4-1-2014DDEEFF
5-1-2014DDEEFF
6-1-2014DDEEFF
7-1-2014DDEEFF
8-1-2014DDEEFF
9-1-2014DDEEFF
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

1 Solution

Accepted Solutions

Re: URGENT: Fill dates with multiple values

See attached qvw.

And read this blog post: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand
2 Replies

Re: URGENT: Fill dates with multiple values

See attached qvw.

And read this blog post: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand
dickelsa
Contributor

Re: URGENT: Fill dates with multiple values

Thanks!

Community Browser