Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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
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;
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