topic Re: Inteval Match in QlikView

Inteval Match

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

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?

Re: Inteval Match

effinty2112 — Wed, 23 Aug 2017 10:13:35 GMT

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

Re: Inteval Match

pathiqvd — Wed, 23 Aug 2017 10:58:10 GMT

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,

Re: Inteval Match

Anonymous — Fri, 25 Aug 2017 07:14:43 GMT

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?