Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
@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 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;
Thanks you @Kushal_Chawda. It worked!