Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Adding a range of dates (Conditional concatenation)

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?

Tags (1)
1 Solution

Accepted Solutions
marcus_sommer
Not applicable

Re: Adding a range of dates (Conditional concatenation)

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

6 Replies
marcus_sommer
Not applicable

Re: Adding a range of dates (Conditional concatenation)

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

Not applicable

Re: Adding a range of dates  (Conditional concatenation)

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

marcus_sommer
Not applicable

Re: Re: Adding a range of dates  (Conditional concatenation)

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

dirk1955
Not applicable

Re: Adding a range of dates (Conditional concatenation)

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:

marcus_sommer
Not applicable

Re: Re: Adding a range of dates (Conditional concatenation)

It is a setting in general tab from document:

- Marcus

dirk1955
Not applicable

Re: Re: Adding a range of dates (Conditional concatenation)

THX for the information and the quick reply.