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