topic Re: How to use IntervalMatch in QlikView

How to use IntervalMatch

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I can't suss out the correct form to achieve what I want from the examples I have found by searching, so I hope someone here could help me. I am using Qlik Sense, not View, if that makes a difference.

We collect commission on in a phased manner, and I want to include the month a payment is due on the month consumption occurs. Each month's consumption for a contract can only have one due month, but there may be many months' consumptions due in the same month for a contract, ie there's a one-to-many relationship between ForecastMonth & ContractMonth. I have stripped the full tables down to this in a test app, but I don't see the ForecastDueMonthStart on the ContractMonth table - what am I doing wrong?

ContractForecast:

Load ContractId as [Contract Id],

StartDate as ForecastDueMonthStart,

ConsumptionPeriodStartDate,

ConsumptionPeriodEndDate

FROM <ContractForecast.QVD>;

ContractMonth:

Load [Contract Id],

[Contract Month Start],

[ContractStartDate],

[ContractEndDate]

FROM <ContractMonth.QVD>;

join IntervalMatch ([Contract Month Start],[Contract Id])

load ConsumptionPeriodStartDate, ConsumptionPeriodEndDate, ForecastDueMonthStart, [Contract Id]

Resident ContractForecast;

drop fields ConsumptionPeriodStartDate, ConsumptionPeriodEndDate from ContractMonth;

Re: How to use IntervalMatch

sunny_talwar — Wed, 18 Jul 2018 10:27:50 GMT

How about this?

ContractForecast:

Load ContractId as [Contract Id],

StartDate as [Contract Month Start],

ConsumptionPeriodStartDate,

ConsumptionPeriodEndDate

FROM <ContractForecast.QVD>;

ContractMonth:

Load [Contract Id],

[Contract Month Start],

[ContractStartDate],

[ContractEndDate]

FROM <ContractMonth.QVD>;

join IntervalMatch ([Contract Month Start],[Contract Id])

load ConsumptionPeriodStartDate, ConsumptionPeriodEndDate, [Contract Month Start], [Contract Id]

Resident ContractForecast;

drop fields ConsumptionPeriodStartDate, ConsumptionPeriodEndDate from ContractMonth;

Re: How to use IntervalMatch

Anonymous — Wed, 18 Jul 2018 11:08:27 GMT

Hi Sunny, thanks for your suggestion, but that didn't work, it just created a synthetic key between the 2 now same-named fields.

I have gone even simpler in my digging, the script now looks like this

ContractForecast:

LOAD * INLINE [

"Contract Id", ForecastDueMonthStart, ConsumptionPeriodStartDate, ConsumptionPeriodEndDate

1, 1/4/18, 1/1/18, 30/3/18

1, 1/7/18, 1/4/18, 30/6/18

2, 1/8/18, 1/7/18, 30/7/18

2, 1/9/18, 1/8/18, 30/8/18

2, 1/10/18, 1/9/18, 30/9/18

];

ContractMonth:

LOAD * INLINE [

"Contract Id", "Contract Month Start"

1, 1/1/18

1, 1/2/18

1, 1/3/18

1, 1/4/18

1, 1/5/18

1, 1/6/18

2, 1/7/18

2, 1/8/18

2, 1/9/18

];

left join IntervalMatch ([Contract Month Start],[Contract Id])

load ConsumptionPeriodStartDate, ConsumptionPeriodEndDate, ForecastDueMonthStart,[Contract Id]

Resident ContractForecast;

I want the resultant ContractMonth to look like this (though I fully expect "Contract Id", ForecastDueMonthStart to be pulled out as a Syn key)

"Contract Id", "Contract Month Start", ForecastDueMonthStart

1, 1/1/18, 1/4/18

1, 1/2/18, 1/4/18

1, 1/3/18, 1/4/18

1, 1/4/18, 1/7/18

1, 1/5/18, 1/7/18

1, 1/6/18, 1/7/18

2, 1/7/18, 1/8/18

2, 1/8/18, 1/9/18

2, 1/9/18, 1/10/18

I hope this make things clearer

Re: How to use IntervalMatch

sunny_talwar — Wed, 18 Jul 2018 11:14:53 GMT

May be try this

ContractForecast:

LOAD * INLINE [

Contract Id, ForecastDueMonthStart, ConsumptionPeriodStartDate, ConsumptionPeriodEndDate

1, 1/4/18, 1/1/18, 30/3/18

1, 1/7/18, 1/4/18, 30/6/18

2, 1/8/18, 1/7/18, 30/7/18

2, 1/9/18, 1/8/18, 30/8/18

2, 1/10/18, 1/9/18, 30/9/18

];

ContractMonth:

LOAD * INLINE [

Contract Id, Contract Month Start

1, 1/1/18

1, 1/2/18

1, 1/3/18

1, 1/4/18

1, 1/5/18

1, 1/6/18

2, 1/7/18

2, 1/8/18

2, 1/9/18

];

Left Join (ContractMonth)

IntervalMatch ([Contract Month Start],[Contract Id])

LOAD ConsumptionPeriodStartDate,

ConsumptionPeriodEndDate,

[Contract Id]

Resident ContractForecast;

Left Join (ContractMonth)

LOAD *

Resident ContractForecast;

DROP Table ContractForecast;

Re: How to use IntervalMatch

Anonymous — Wed, 18 Jul 2018 13:00:44 GMT

Hi Sunny

Thanks for this - I will have to tinker around the edges as there is more payload to the ContractForecast table (and to work out why it works - it looks a bit non-intuitive at first sight).

Will mark as Correct Answer.

Regards, Stewart