Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've got a table where a new line is generated when something happens to a customer. Now I want to create an overview of daily development of the outstanding balance on customers. I'm thinking I need to do an interval match to create an daily overview, but I'm just not able to make it work. Can someone here be of assistance, pretty please?
So, this is a part of my load script so far:
CustomerHistory:
LOAD
CustomerID,
Account_no ,
Outstanding_total_balance,
Claim_status,
Delinquency_status,
date(scd_start) as scd_start,
date(scd_end) as scd_end,
scd_active,
id as Historyid
FROM
TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
Calendar
LOAD
date(TempDate) As Date
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
BridgeTable:
IntervalMatch(Date)
Load distinct scd_start,scd_end resident CustomerHistory;
Drop Table BridgeTable;
I've found some very good articles on this, but somehow my brain is not able to comprehend this.. Maybe there is an entirely different method that works?
Hi Xili,
You shouldn't drop the BridgeTable. Don't worry about the resulting synthetic key, it's expected and will not damage performance.
Kind regards
Andrew
Hi,
Try this,
Remove drop statement,
CustomerHistory:
LOAD
CustomerID,
Account_no ,
Outstanding_total_balance,
Claim_status,
Delinquency_status,
date(scd_start) as scd_start,
date(scd_end) as scd_end,
scd_active,
id as Historyid
FROM
TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
Calendar
LOAD
date(TempDate) As Date
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
Left Join(CustomerHistory) // or inner join(CustomerHistory)
BridgeTable:
IntervalMatch(Date)
Load distinct scd_start,scd_end resident CustomerHistory;
if you want remove syn keys Join with this CustomerHistory .
and also you need to check Date, scd_start, scd_end fields date format.
Regards,
I was hoping to create a table that consists of date and Outstanding_total_balance. However, the sum(Outstanding_total_balance) doesn't give me the correct sum. Am I being naive, thinking a simple expression like that would work? Do I need to manipulate the data more, somehow?