2 Replies Latest reply: Oct 10, 2014 9:05 AM by dick elsman RSS

    URGENT: Fill dates with multiple values

    dick elsman

      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