2 Replies Latest reply: May 22, 2017 9:04 AM by Fer Fer RSS

    Question comparison dates - intervalmatch

    Jean-Michel Achallé

      Hello,

      New user of Qlikview, I am training on the job, and is currently encountering difficulty in the preparation of an analysis.

      I try to connect a calendar to my data, and more specifically, when I select a date, it filters all the people between two dates (entry and exit) - the number of people is very important.

       

      The IntervalMatch function appeared to me the most appropriate to connect the tables.

      I first tried the function with a test by entering the data discretely below.

       

      DatesData:

      LOAD * Inline [

          Test_Date

          11/01/2013

          12/01/2013

          01/01/2014

          02/01/2014

          03/01/2014

          04/01/2014

          05/01/2014

      ];

       

      Persondata:

      LOAD * Inline [

      ID, Start_Date, End_Date

          1, 12/01/2013, 02/01/2014

          2, 01/01/2013, 03/01/2014

          3, 02/01/2014, 04/01/2014

      ];

       

      INNER JOIN (DatesData)

      IntervalMatch (Test_Date)

      LOAD

          start_date,

          END_DATE

      Resident PersonData;

       

      JOIN (DatesData)

      LOAD

          *

      RESIDENT PersonData;

       

      DROP TABLE PersonData;

       

       

      The result is convincing.

       

      However, as soon as I want to do the same with my actual data, included in tables loaded directly in Qlikview, errors appear and i do not understand their origin:

       

       

      I have the following tables:

      "Data" containing various fields, including "ID" (number), "Enter" (date) and "Exit" (date).

      "DatesData"

       

       

      My script is:

      SET ThousandSep = '';

      SET DecimalSep = ',';

      SET MoneyThousandSep = '';

      SET MoneyDecimalSep = ',';

      SET MoneyFormat = '# ## 0,00 €; - # ## 0,00 €';

      SET TimeFormat = 'hh: mm: ss';

      SET DateFormat = 'DD / MM / YYYY';

      SET TimestampFormat = 'DD / MM / YYYY hh: mm: ss [.fff]';

      SET FirstWeekDay = 0;

      SET BrokenWeeks = 1;

      SET ReferenceDay = 0;

      SET FirstMonthOfYear = 1;

      SET CollationLocale = 'en-GB';

      SET MonthNames = 'Jan, Feb., March, Apr., May, June, Jul, Aug, Sep. Oct. Nov. Dec .;

      SET LongMonthNames = January, February, March, April, May, June, July, August, September, October, November, December;

      SET DayNames = 'Mon, Tue; Wed, Thu Fri Sat Sun';

      SET LongDayNames = 'Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday';

       

       

      Data:

      LOAD

           ID,

           Entrance,

           Exit,

      FROM

      [Excel file path]

      (Ooxml, embedded labels, table is [ExcelFieldName]);

       

       

      DatesData:

      LOAD Test_Date

      FROM

      [Excel file path]

      (Ooxml, embedded labels, table is [ExcelFieldName]);

       

       

      INNER JOIN (DatesData)

      IntervalMatch (Test_Date)

      LOAD Distinct

          Entrance,

          Exit

      Resident Data;

       

      JOIN (DatesData)

      LOAD

          *

      RESIDENT DATA;

       

      DROP TABLE Data;

       

      In this case, the IntervalMatch function does not function properly and selects data outside of the interval.

       

      Could you help me resolve this problem please?