left join on conditions

[Ori]

hi guys,

I got a sql query that I wish to transfer to Qlik syntax.

basically the sql query creates an outer join between a date range and a cluster_id list so that each cluster has all the date range and then another join adds another field ("event_id") based on a combined on condition.

now up to the outer join no problems, handled that quite easily.

the addition of the event id is the tricky part, each event id has a begin date and an end date.

in sql we simply use an on condition where the timestamp is greater than the beginning date and lower than the end date.

it was hinted that the solution might be related to the interval match function but I can seem to get the syntax right.

example of table one (timestamp and cluster_id) 

example of table 2 - out of which i want to return the event id based on the timestamp and the cluster id (each event id exists under a specific cluster)

end result :

emphasis, the existence on blank timestamps  in which there are no event id's are important, we want to maintain the full timestamp range even if there are no event ids for the entire range.

thanks!

ori.

[Ori · Accepted solution]

Rubber duck debugging and a bit of online search did the trick.

Left Join (table a)

IntervalMatch(timestamp1, cluster_id)
LOAD created_at,
end_date,
cluster_id
Resident table_b;

and then another left join to get the event id

left join (table a)

load
created_at,
end_date,
cluster_id,
event_id

resident table_b;

perhaps it could have been done more efficiently, if anyone knows of a way i'd love to hear it.