How to use IntervalMatch

[Anonymous]

Hi

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;

[sunny_talwar]

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;

[Anonymous]

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

[sunny_talwar · Accepted solution]

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;

[Anonymous]

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