5 Replies Latest reply: May 11, 2016 1:55 PM by Ruben Marin RSS

    Canonical Date with no Distinct Key

    Gary McDonald



      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


      Claims Table:


      Policy NumberClaims NumberClaim Opened DateClaim Settled DateClaim Rejected Date


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


      Thanks in advance


        • Re: Canonical Date with no Distinct Key
          Brian Copple

          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.



          • Re: Canonical Date with no Distinct Key
            Gysbert Wassenaar

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

              • Re: Canonical Date with no Distinct Key
                Gary McDonald

                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.




                  • Re: Canonical Date with no Distinct Key
                    Ruben Marin

                    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.

                • Re: Canonical Date with no Distinct Key
                  Ruben Marin

                  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.