Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Vinerc
Contributor II
Contributor II

IntervalMatch not getting some values

I had a question on how to interval match and it was answered here: https://community.qlik.com/t5/QlikView-Scripting/Intermatch-count-with-several-lines/m-p/1745676#M10....

It worked fine and I was doing some analysis and realized it was just getting 90% of my values on the IntervalMatch and I can't figure out why...

I have two databases: 
1ª: Travels: With the car, StartTime and EndTime
2ª Passengers: With the car and Time.

and I want the number of passengers on each travel. It was working fine but is not getting all the values, I have attached and example and it would be great if someone could help me.

On the image, the frist two trips should have passengers.

Vinerc_0-1601042304121.png

Here is the code:

[BI]:
LOAD Car,
date(StartTime,'DD/MM/YYYY hh:mm:ss') as StartTime,
date([EndTime],'DD/MM/YYYY hh:mm:ss') as EndTime
FROM
[C:\Users\viner\Desktop\Travels.xlsx]
(ooxml, embedded labels, table is Sheet1);

[PRODATABASE]:
LOAD Car,
Time
FROM
[C:\Users\viner\Desktop\Passengers.xlsx]
(ooxml, embedded labels, table is Document_TB07);
Left join
IntervalMatch (Time,Car)
LOAD StartTime,EndTime, Car
Resident BI;


LEFT Join (BI)
LOAD count(Car) as Passengers, StartTime,EndTime,Car
Resident PRODATABASE
Group by StartTime,EndTime,Car
;
Drop table PRODATABASE;

1 Solution

Accepted Solutions
Kushal_Chawda

@Vinerc  try rounding of your timestamp to correct representation of time

Data:
[BI]:
LOAD Car,
timestamp(floor(StartTime,1/24/60)) as StartTime,
timestamp(floor(EndTime,1/24/60)) as EndTime
FROM
[\Travels.xlsx]
(ooxml, embedded labels, table is Sheet1);

[PRODATABASE]:
LOAD Car,
timestamp(floor(Time,1/24/60)) as Time
FROM
[\Passengers.xlsx]
(ooxml, embedded labels, table is Document_TB07);
Left join

IntervalMatch (Time,Car)
LOAD StartTime,EndTime, Car
Resident BI;

LEFT Join (BI)
LOAD count(Car) as Passengers, StartTime,EndTime,Car
Resident PRODATABASE
Group by StartTime,EndTime,Car
;
Drop table PRODATABASE;

View solution in original post

2 Replies
Kushal_Chawda

@Vinerc  try rounding of your timestamp to correct representation of time

Data:
[BI]:
LOAD Car,
timestamp(floor(StartTime,1/24/60)) as StartTime,
timestamp(floor(EndTime,1/24/60)) as EndTime
FROM
[\Travels.xlsx]
(ooxml, embedded labels, table is Sheet1);

[PRODATABASE]:
LOAD Car,
timestamp(floor(Time,1/24/60)) as Time
FROM
[\Passengers.xlsx]
(ooxml, embedded labels, table is Document_TB07);
Left join

IntervalMatch (Time,Car)
LOAD StartTime,EndTime, Car
Resident BI;

LEFT Join (BI)
LOAD count(Car) as Passengers, StartTime,EndTime,Car
Resident PRODATABASE
Group by StartTime,EndTime,Car
;
Drop table PRODATABASE;
Vinerc
Contributor II
Contributor II
Author

Thanks you @Kushal_Chawda. It worked!