calculating based time range

[eladberko]

hello,

i want to do calculations based a time range between 2 tables, in qlik script only.

the main table is a list of leads, the second is phone calls log. i need to count calls for each lead from the lead insert date up to 45 days forward all so get the max duration for that period.

thank you.

here some example for the data model:

 

[Vegar · Accepted solution]

You can use interval match for this. Something similar to this will allow you to make the calculations you need.

Calls:

Load

call_date, phone_number, call_duration

From calls;

Leads:

Load

lead_date as lead_period_start,

lead_date+45 as lead_period_end,

phone_number

From leads;

Intervalmatch (call_date, phone_number)

Load

lead_period_start,

lead_period_end,

phone_number

Resident Leads;