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

    Canonical Date with no Distinct Key

    Gary McDonald

      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

        • 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.

          Regards

          Brian

          • 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.

                 

                Thanks

                Gary

                  • 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.