Question comparison dates - intervalmatch

Sun, 21 May 2017 19:10:43 GMT

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?

Re: Question comparison dates - intervalmatch

malini_qlikview — Mon, 22 May 2017 12:00:48 GMT

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

Re: Question comparison dates - intervalmatch

el_aprendiz111 — Mon, 22 May 2017 13:04:34 GMT

Hi,

IntervalMatch

topic Re: Question comparison dates - intervalmatch in QlikView

Question comparison dates - intervalmatch

Re: Question comparison dates - intervalmatch

Re: Question comparison dates - intervalmatch