Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

2 Replies
jason_michaelid
Not applicable

Re: Self Join

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

flipside
Not applicable

Re: Self Join

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