6 Replies Latest reply: Sep 10, 2013 10:21 AM by Dirk De Wispelaere RSS

    Adding a range of dates  (Conditional concatenation)

    Erik van hout

      As a result I would like a table with the following fields:

           - Date

           - Process

           - UOM

           - Hours

           - Volumes

       

      I'm getting two date-fields from text-files: date_from and date_to

      I would like to get all dates within this range and add those dates (1-by-1) to my result-Table.

      In my Solution I've added a MasterCalendar. So I would need all individual dates in my result-Table.

       

      My script so far:

       

      TempData:                    // I'm using a TempTable as many more columns are imported from the TextFiles

      LOAD      date_from,

                     date_to,

                     Process,

                     UOM,

                     Hours,

                     Volumes

      FROM      $(TextFiles);

       

      ActualData:

      LOAD     date_from                    as %Date,

                     date_to - date_from     as NumberOfDays,

                     Process,

                     UOM,

                     Hours,

                     Volumes

      RESIDENT TempData;

       

      The ActualData is what I expect it to be.

      The column NumberOfDays will be 0 (date_from = date_to) most of the time.

      But it is possible that for one Process (eg. Inbound) a date range for the whole week has been set.

      In what way can I concatenate only those records to my table [ActualData] where NumberOfDays doesn't equal 0?

        • Re: Adding a range of dates  (Conditional concatenation)
          Marcus Sommer

          You need a loop which generated the additional date-values. This could be done with a while-statement within the load - see the example (was for another case but the logic is the same.

           

          - Marcus

            • Re: Adding a range of dates  (Conditional concatenation)
              Erik van hout

              Hi Marcus,

               

              I've just started to use QlikView and QlikCommunity.

              Sounds like the example is exactly what I'm looking for, but I can't find it.

              Can you tell me how to get to the example?

               

              Thanks in advance

               

              Erik

                • Re: Re: Adding a range of dates  (Conditional concatenation)
                  Marcus Sommer

                  If you have a licenced version you could simply download the attachment - if not you are depend only on code and explanations here - this is the code from the example:

                   

                  LET vBeginn = Date('01.01.2014', 'DD.MM.YYYY');
                  LET vEnde = Date('31.12.2018', 'DD.MM.YYYY');
                  
                  tmp_Klienten:
                  LOAD * INLINE [
                      Klientennr, Kostenstelle, ZugangDatum, AbgangDatum
                      1451, 50115, 01.01.2013, 31.07.2014
                      1451, 30228, 01.08.2014, 31.12.2200
                      9512, 50115, 01.03.2012, 04.12.2013
                      9512, 40168, 05.12.2013, 30.06.2014
                      9512, 25997, 21.07.2014, 31.12.2200
                      9599, 50115, 01.05.2014, 31.12.2015
                      9600, 50115, 15.01.2014, 31.10.2016
                      9601, 50115, 01.01.2013, 31.12.2013
                  ];
                      
                  tmp_BELEGUNG2:
                  LOAD *,
                      rowno() as DS,
                      Kostenstelle &'-' &Klientennr &'-' &ZugangDatum AS SCHLÜSSEL,
                       IF(DATE(ZugangDatum,'YYYYMM') = DATE(AbgangDatum,'YYYYMM'), AbgangDatum - ZugangDatum + 1,
                           IF(Iterno() = 1, MONTHSEND(1, ZugangDatum) - ZugangDatum,
                               IF(DATE(ADDMONTHS(ZugangDatum, iterno() -1, 1), 'YYYYMM') = DATE(AbgangDatum,'YYYYMM'), DAY(AbgangDatum), // If last Month just take day of DatBis as amount of days
                                   DAY(MONTHSEND(1, ADDMONTHS(ZugangDatum, iterno() -1, 1)))))) AS AnzahlTage,
                      MonthStart(DATE(ADDMonths(ZugangDatum, iterno() -1, 1), 'MM.YYYY')) AS MonatJahr
                  RESIDENT tmp_Klienten
                  WHILE DATE(ADDMONTHS(ZugangDatum, iterno() -1, 1), 'YYYYMM') <= DATE(AbgangDatum,'YYYYMM');
                  //and MonthStart(DATE(ADDMonths(ZugangDatum, iterno() -1, 1), 'MM.YYYY')) >= DATE(MinDate,'MM.YYYY') --> funktioniert nicht ???
                  //and MonthStart(DATE(ADDMonths(ZugangDatum, iterno() -1, 1), 'MM.YYYY')) <= DATE('$(vEnde)','MM.YYYY'); --> funktioniert
                  
                  BELEGUNG:
                  NoConcatenate LOAD * RESIDENT tmp_BELEGUNG2 WHERE MonatJahr >= '$(vBeginn)' AND MonatJahr <= '$(vEnde)';
                  
                  DROP TABLES tmp_Klienten, tmp_BELEGUNG2;
                  
                  

                   

                  - Marcus

                • Re: Adding a range of dates  (Conditional concatenation)
                  Dirk De Wispelaere

                  Hi Marcus,

                   

                  Where did you configure the layaout of your table?

                  I don't have the option "use borders"

                  Your lay-out:

                   

                  My lay-out tab: