2 Replies Latest reply: Oct 5, 2012 5:26 AM by Dave Riley RSS

    Self Join

      Hi all,

       

      I'm looking for a way to create a self join within qlikview to take a single sales table and create a self join on Reference number where a policy has been renewed.

       

      For example if a Sales policy was due to end on the 30/09/2012, but was renewed on the 01/10/2012, I would like to create a table where both records are on 1 line.

       

      Ideally I want to create a process where, if the end user selects a date range , the process will look at the policy end dates in that date range, then search for an end date 1 year on to find and new end date based on policy reference number.

       

      I have enclosed and excel representation of what I am trying to achieve, hope it makes sense.

       

      I'm new to qlikview, so any help / tips would be most appreciated.

       

      Thanks in advance.

        • Re: Self Join
          Jason Michaelides

          I think your data model is probably right as it is. If you try and change it in the script, where do you stop - after 1 year? What about policies that have been renewed 2,3 or 4 times? You'll end up with an endlessly wide table.

           

          You can achieve what you want with set analysis. Create a straight table chart with your Policy Number as a dimension. Then, as your expressions:

           

          Original Start Date

          =Only({<[Current Start Date] = {">=$(=(Min(Date))<=$(=(Max(Date))"}>} [Current Start Date])

           

          New Start Date

          =Only({<[Current Start Date] = {">=$(=(AddYears(Min(Date),1))<=$(=(AddYears(Max(Date),1)"}>} [Current Start Date])

           

          Where Date is some kind of calendar table field.

          Then do the same with End Dates and your other fields.

           

          I'm on the train so haven't tested this and I might have the syntax slightly wrong! Let us know how you get on

           

          Hope this helps,

           

          Jason

          • Re: Self Join
            Dave Riley

            If it was me I would keep these as two separate tables linked on the reference number, something like this in the script ...

             

            RefNumbers:
            LOAD DISTINCT
            [Current ReferenceNumber] AS RefNo,
            [Current Insurer] as Insurer,
            [Current Affiliate ID] as AffiliateID
            resident RawData;

             

            PolicyDates:
            LOAD
            [Current ReferenceNumber] AS RefNo,
            [Current Start Date] as PolicyStartDate,
            [Current End Date] as PolicyEndDate,
            [Current Net Premium] as PolicyNetPremium,
            if([Current Start Date]<=Now() and [Current End Date]>=Now(),'Live',
              if([Current Start Date]>Now(),'Pending',
               'Expired')) as PolFlag
            resident RawData;

             

            This would allow you to build a pivot table with ref no and PolFlag (or status) as dimensions (PloFlag laid out horizontally).

             

            example.PNG

             

            flipside