joining two internal tables, referencing fields from both in join
hi I have two resident tables:
"DOWNTIME" with fields LocationId and DtmStarts, DtmEnds
"SHIFTS" with fields LocationId, ShiftStarts, ShiftEnds
The joining logic is to see where they overlap, thus in SQL the join would have looked like this:
Select * from DOWNTIME JOIN SHIFTS ON DOWNTIME.LocationId = SHIFTS.LocationId
and DtmEnds > ShiftStarts
and DtmStarts < ShiftEnds
The issue is how to do this in Qlikview? It is not a simple associative match. What is the syntax of joining two internal tables, referencing fields from both tables?
One option was to do the associative match on LocationId into temp table, and then extract from the temp table using the following in the WHERE clause:
and DtmEnds > ShiftStarts
and DtmStarts < ShiftEnds
However, the resulting temp table would be too big for the model to reload. The other option I know of is the rangematch, but this would be complicated because both tables have ranges.
