Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Inteval Match

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?

3 Replies
Highlighted
Master
Master

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

Highlighted
Creator III
Creator III

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,

Highlighted
Contributor II
Contributor II

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?