Qlik Sense and Type 2 slowly changing dimensions

[francescoleoni2]

Hi there,

I'm trying to implement a Qlik sense dashboard reading data from an historical DW with Type SCD. This means that all the tables have a snapshot of the data at any point in time.

I attached a sample of the data (example data) with claim, claimant and payment information.

now, since I have different dates to filter, I was wondering if there is a way of linking correctly the tables.

like I need to be able to see the data at 09/09 (see example data) but if I filter for October, I need to see the status and the payments related to October, which for example is the latest value only (PaymentID 1)

ClaimID	ClaimStatus	EffectiveDate
1	Created	09-Sep
2	Created	03-Oct
1	Registered	09-Sep
1	Assigned	10-Sep

ClaimantID	ClaimID	ClaimantStatus	EffectiveDate	ClaimantName
1	1	Created	09-Sep	Name 1
2	1	Payments Made	05-Oct	Name 1
3	2	Final Payment	06-Oct	Name 2

PaymentID	ClaimantID	Payment Date	Amount
1	1	10-Sep	10
1	1	05-Oct	15
2	1	06-Oct	5
3	2	06-Oct	10

Thanks in advance,

Francesco

[francescoleoni2]

ClaimID	ClaimStatus	EffectiveDate
1	Created	03-Sep
2	Created	03-Oct
1	Registered	09-Sep
1	Assigned	10-Sep

[Mark_Little]

HI,

Take a look at the link below.

Why You sometimes should Load a Master Table several times

I know it is Written for QlikView but it will work for QlikSense as well.

Mark

[agigliotti]

you should provide us a sample qvf file to well understand what 's your expected result.

[ogster1974]

Looking at what you have id be suggesting a data model with a fact table built around the Claim, Claimant and Payment Dimensions

Facts I can see are

Created

Registered

Assigned

Payment

I assume they'd be cancellations and rejections and escalations to be added to reflect the workflow.

ClaimantId, ClaimId, PaymentId, Status, Date, Comments, Amount

Would probably work as a fact table structure.

Load each state into that fact table populating what you can and then distinct your dimension data into the dimension tables you will then have a star schema you can play with in lots of ways.

Final or latest states to be stored against each dimension.

Looks like you may have a missing relationship between your payment and what it is for.  You are authorising a payment for a claim at the moment you have no context for what it is for.  Also id guess the payment may be to a third party beneficary not just a claimant but thats just me over thinking it.

Anyway hope this helps

Regards

Andy

[francescoleoni2]

Hi Andy,

Thank you for your reply.

You may be on the right track but you're missing the point.

my problem is not to link those together. is to link the different dates. I'll explain better.

in the DW I have the historical data of my client. (insurance) the tables I shared are just example because I cannot share anything for data protection reasons.

Let's say that I am at the beginning of November and I need to display quarter end reports. I need to show what happened until the 30th of September. so if my data set for the claim is

1	Created	03-May
2	Created	05-Sept
1	Registered	09-Sep
1	Assigned	10-Oct
2	Registered	25-Oct

so I need to show that claim 1 is Registered and claim 2 is created, but if I select the end of October to see the month end reports I need to show that Claim 1 is Assigned and Claim 2 is Registered.

At the same time, and this is the issue,

I need to show the same logic but on the claimants on the claim date and for the payments on the payment date, to show all the payments made at that point with the correct status.

PaymentID	ClaimantID	Payment Date	Amount
1	1	10-Sep	10
1	1	05-Oct	15
2	1	06-Sept	5
3	2	06-Oct	10

for the quarter end, I need to show Payment 1 with amount of 10 , payment 2 with amount 5 and the total some is 15. while for the October report, Payment 1 is now 15 and I have also payment 3 with amount 10 so the total sum is 30.

So, I'm trying to find a way to link everything together.

Now what comes in mind is set analysis. but I would prefer another way.

Thanks for your help.

[francescoleoni2]

Hi,  yes that might me a solution, but how do you connect the 2 dates to have the same selection in the 2 calendars?

[luismadriz]

Hi Francesco,

I'd recommend to validate your data against a functional design, meaning comprehend what the actions are and how the data reflect those actions. With a quick look at your data, I would try joining Claims and Payments in a Transaction table and leaving Claimants as the SCD... although I couldn't figure out what was the value that would change for a claimant.

For example in Oracle eBusiness (R12) when you create a transaction against an Inventory Item, the system saves in the transaction table the unit of measure and relationship against the base unit of measure. This is because the UoM of an item can be changed at any time but the transactions will remain with the information they need regardless. Oracle could have linked UoMs and transactions by the dates but this would have been costly in terms of performance without adding much value.

Going back to your design, or at least to the data you provided, you could attempt to link transactions with Claimant but make sure that each transaction refers to only one dimension (claimant), for which you may need a composite key.

Of course, this is just quick recommendation base on a 5-min analysis as I'm not familiar with your business or your data and I agree with you in trying to create a model that will simplify your life in terms of set analysis

All the best

Luis

[gino2780]

Hello, i haven't read everything so i just reply to your question at this point of the discussion: why don't you try to join the data by date and then join the combined table with a master- or autocalendar so that you can select data just by date.