Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
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;

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda
MVP
MVP

@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
MVP
MVP

@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!