Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

francescoleoni2
New Contributor

Qlik Sense and Type 2 slowly changing dimensions

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)

 

ClaimIDClaimStatusEffectiveDate
1Created09-Sep
2Created03-Oct
1Registered09-Sep
1Assigned10-Sep

 

ClaimantIDClaimIDClaimantStatusEffectiveDateClaimantName
11Created09-SepName 1
21Payments Made05-OctName 1
32Final Payment06-OctName 2

PaymentIDClaimantIDPayment DateAmount
1110-Sep10
1105-Oct15
2106-Oct5
3206-Oct10

Thanks in advance,

Francesco

7 Replies
Highlighted
francescoleoni2
New Contributor

Re: Qlik Sense and Type 2 slowly changing dimensions

ClaimIDClaimStatusEffectiveDate
1Created03-Sep
2Created03-Oct
1Registered09-Sep
1Assigned10-Sep
mark6505
Valued Contributor III

Re: Qlik Sense and Type 2 slowly changing dimensions

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
Honored Contributor II

Re: Qlik Sense and Type 2 slowly changing dimensions

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

ogster1974
Honored Contributor II

Re: Qlik Sense and Type 2 slowly changing dimensions

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
New Contributor

Re: Qlik Sense and Type 2 slowly changing dimensions

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

1Created03-May
2Created05-Sept
1Registered09-Sep
1Assigned10-Oct
2Registered25-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.

PaymentIDClaimantIDPayment DateAmount
1110-Sep10
1105-Oct15
2106-Sept5
3206-Oct10

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
New Contributor

Re: Qlik Sense and Type 2 slowly changing dimensions

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
Valued Contributor

Re: Qlik Sense and Type 2 slowly changing dimensions

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

Community Browser