Inteval Match

[Anonymous]

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 (qvd);

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?

[effinty2112]

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

[pathiqvd]

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 (qvd);

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,

[Anonymous]

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?