Canonical Date with no Distinct Key

[#1]

Hello,

I'm a basic Qlikview user and am struggling to build a dashboard within the requirements due to the below issue.

I have followed the guide posted by hic‌Canonical Date on Canonical dates however I am unable to find a way to make it work with my data.

I think it is due to being unable to get a table with a fine enough grain.

Any help or advise would be greatly appreciated.

I have two tables. a policy table and a claims table.

Basic Example.....

A policy can have many rows (as shown in the below tables), and a policy can also have many claims (example below).

Policy Table:

Policy Number	Transaction Date	Inception Date	Cancellation Date	Transaction Type
Pol_1	15/01/2016	01/02/2016	-	New Business
Pol_1	03/02/2016	01/02/2016	-	Adjustment
Pol_1	04/03/2016	01/02/2016	-	Adjustment
Pol_1	05/03/2016	01/02/2016	05/03/2016	Cancelled

Claims Table:

Policy Number	Claims Number	Claim Opened Date	Claim Settled Date	Claim Rejected Date
Pol_1	Matt1	14/02/2016	21/02/2016	-
Pol_1	Matt6	16/02/2016	17/02/2016	-
Pol_1	Matt12	22/02/2016	-	04/03/2016

Attached is my current table view, but I am getting duplicate data from the Canonical Calendar

Thanks in advance

Gary

[Anonymous]

Hi Gary

I don't understand why you have a canonical calendar associated to the link table but then also have three separate calendars for transaction, policy and cancellation. Can you provide this segment of your script please so I can better understand your issue.

Regards

Brian

[Gysbert_Wassenaar]

Can you post a small qlikview document that demonstrates the actual problem?

[#4]

Hi Gysbert,

I have attached a QVW, the dashboard is very basic.

On the selected example (Unique PolicyNumber =PET/W/50056068/PLUS 03/01/2016) I should have:

Only one transaction count in in Jan-2016 (not on Fab-16)

One count in Jan-2016 for a claim (MatterID 50056143) & one Count for March-2016 (MatterID 50060146)

I should point out that not all policys will have a claim or a cancellation.

Thanks

Gary

[rubenmarin]

Hi Gary, from what I see you're right, you need more granularity to follow that guide, some field that is unique in each row like a PolicyTransactionId... I guess (I don't know how the policy_canonical field is created) that now is doing:

If you select Month=january and [date type]=transaction, the DateLink table will say: 'ok, I have one of those: the policy Pol_1', so it goes to the PolicyLoadTable and returns all records with Pol_1 as policy_canonical.

[rubenmarin · Accepted solution]

Hi Gary, your expressions can be fixed yo work with that model, ie, for claims opened:

Count({<DateType={'Opened'}>} If(MonthYear=ClaimOpenedMonthYear, MatterID))

Edit: and about the transacted... maybe adding a DISTINCT clause... Or an aggr between Policynumber and transaction date... I don't really know how to count 2 different transactions made by one policy in one day.