Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Canonical Date with no Distinct Key

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 hicCanonical 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 NumberTransaction DateInception DateCancellation DateTransaction Type
Pol_115/01/201601/02/2016-New Business
Pol_103/02/201601/02/2016-Adjustment
Pol_104/03/201601/02/2016-Adjustment
Pol_105/03/201601/02/201605/03/2016Cancelled

Claims Table:

    

Policy NumberClaims NumberClaim Opened DateClaim Settled DateClaim Rejected Date
Pol_1Matt114/02/201621/02/2016-
Pol_1Matt616/02/201617/02/2016-
Pol_1Matt1222/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

1 Solution

Accepted Solutions

Re: Canonical Date with no Distinct Key

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.

5 Replies
briancopple
Contributor II

Re: Canonical Date with no Distinct Key

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

Re: Canonical Date with no Distinct Key

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


talk is cheap, supply exceeds demand
Not applicable

Re: Canonical Date with no Distinct Key

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

Re: Canonical Date with no Distinct Key

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.

Re: Canonical Date with no Distinct Key

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.

Community Browser