Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Can you please let us know what error you get while executing the script?