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.
