topic Re: Self Join in QlikView

Self Join

Thu, 04 Oct 2012 14:52:30 GMT

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 — Fri, 05 Oct 2012 08:31:37 GMT

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

flipside — Fri, 05 Oct 2012 09:26:08 GMT

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

flipside