Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Question comparison dates - intervalmatch

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?

2 Replies
malini_qlikview
Contributor II

Re: Question comparison dates - intervalmatch

Can you please let us know what error you get while executing the script?

el_aprendiz111
Valued Contributor

Re: Question comparison dates - intervalmatch