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,



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


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


            [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).