topic Re: IntervalMatch not getting some values in QlikView

IntervalMatch not getting some values

Vinerc — Fri, 25 Sep 2020 13:59:53 GMT

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#M100064.

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.

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;

Re: IntervalMatch not getting some values

Kushal_Chawda — Fri, 25 Sep 2020 14:39:39 GMT

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

Re: IntervalMatch not getting some values

Vinerc — Fri, 25 Sep 2020 17:56:02 GMT

Thanks you @Kushal_Chawda. It worked!