Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agaetisproject
New Contributor III

Contract status and renewal

Dear all,

I need some help from the community on a question concerning contract status and renewal.

I need to compute the number of active, expired and future contract, say for an insurer.

The original data contains:

- the ContractID

- the S/N (typically an ID for the person or object insured)

- the StartDate of the contract

- the EndDate of the contract.

See table below for a representative sample.

Could you help me compute the Calculated Dimensions in the script as follows?

- Contract status (this one is pretty simple as it can be computed on a row per row basis)

- Contract renewal status is much more tricky as it requires combining multiple rows and columns)

As you can see, the rules are pretty simple:

- a contract that is Expired/Active/Future should be flagged as Renewed when it is followed by another contract for the same S/N.

- a contract that is Expired should be flagged as Not renewed when there is no contract for the same S/N.

- a contract that is Active/Future should be flagged as Not yet Renewed when it is not yet followed by another contract for the same S/N.

Please note that there are some gaps between the start date/end date in some records so we cannot rely on successive checks there.

Cherry on the cake, compute the contract renewal rate KPI :-)

I browsed many similar post but could not find an answer while I am sure many people out there face the exact same issue.

Can you help?

    

Original dataCalculated dimensions
ContractIDS/NStartDateEndDateContract statusContract renewal status
100101.01.201230.06.2012ExpiredRenewed
101115.07.201230.06.2013ExpiredRenewed
102102.07.201330.06.2016ActiveNot yet renewed
103201.01.201230.06.2014ExpiredNot renewed
104301.01.201130.06.2013ExpiredRenewed
105301.07.201330.06.2016ActiveRenewed
106301.07.201630.06.2018FutureNot yet renewed
107401.07.201630.06.2018FutureNot yet renewed
108501.01.201230.06.2012ExpiredRenewed
109515.07.201230.06.2013ExpiredNot renewed
110601.07.201330.06.2016ActiveRenewed
111601.07.201630.06.2018FutureRenewed
112601.07.201830.06.2020FutureNot yet renewed
1 Solution

Accepted Solutions
MVP
MVP

Re: Contract status and renewal

I think I've overcomplicated things before, you don't need the aggregation and join step.

LOAD ContractID,

    StartDate,

    EndDate,

    [S/N],

    [Contract status]

//    [Contract renewal status] as Check2,

//    Expected

FROM

[https://community.qlik.com/thread/172582]

(html, codepage is 1252, embedded labels, table is @2);

LOAD ContractID,

  [S/N],

  StartDate,

  EndDate,

  [Contract status],

  if(peek([S/N]) <> [S/N], if([Contract status]<>'Expired', 'Not yet renewed','Not renewed'),'Renewed') as [Contract renewal status]

//  Expected,

//  Check2

Resident INPUT

ORDER BY [S/N], ContractID desc;

DROP TABLE INPUT;

7 Replies
MVP
MVP

Re: Contract status and renewal

I just worked on the renewal status and used the Contract status from your excel file (since it seems to be easy to implement):

INPUT:

LOAD ContractID,

     [S/N],

     StartDate,

     EndDate,

     [Contract status],

     [Contract status] as CS,

     [Contract renewal status] as Check2

FROM

[Contract example.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 1))

));

LEFT JOIN (INPUT)

LOAD [S/N],

  count([S/N]) as CountSN

RESIDENT INPUT

GROUP BY [S/N];

LOAD ContractID,

  [S/N],

  StartDate,

  EndDate,

  [Contract status],

  if(peek([S/N]) <> [S/N], if(CountSN>1 or CS='Future', 'Not yet renewed','Not renewed'),'Renewed') as [Contract renewal status]

// Check2,

// CountSN

Resident INPUT

ORDER BY [S/N], ContractID desc;

DROP TABLE INPUT;

agaetisproject
New Contributor III

Re: Contract status and renewal

Million thanks Swuehl! This is extremely helpful.

I am really impressed by both the short lead time and your excellent answer.

I tested it on my end tonight and it works almost perfectly.

Nothing wrong done on your end, just that, as always, real data are slightly more complex than the data provided in my original sample and I noticed two edge cases:

- Edge case 1: there are often many S/N under one contract and in that case, the 'Renewed' or 'Not yet renewed' logic does not provide the expected results

- Edge case 2: when a machine had two (or more) contract that expired in the past, the status turns to 'Not yet renewed' for the second contract while I expected a 'Not renewed'

Could you kindly indicate me how to tweak the last part of your script to account for such edge cases?

Again, thanks for your very precious help!

   

ContractIDStartDateEndDateS/NContract statusContract renewal statusExpected
10001.01.201230.06.20121ExpiredRenewed
10115.07.201230.06.20131ExpiredRenewed
10202.07.201330.06.20161ActiveNot yet renewed
10301.01.201230.06.20142ExpiredNot renewed
10401.01.201130.06.20133ExpiredRenewed
10501.07.201330.06.20163ActiveRenewed
10601.07.201630.06.20183FutureNot yet renewed
10701.07.201630.06.20184FutureNot yet renewed
11001.07.201330.06.201616ActiveRenewed
11101.07.201630.06.201816FutureRenewed
11201.07.201830.06.202016FutureNot yet renewed
10801.01.201230.06.201255ExpiredRenewed
10915.07.201230.06.201355ExpiredNot yet renewedNot renewed
10001.01.201230.06.2012123ExpiredRenewed
10115.07.201230.06.2013123ExpiredRenewed
10202.07.201330.06.2016123ActiveNot yet renewed
10202.07.201330.06.2016124ActiveNot renewedNot yet renewed
10202.07.201330.06.2016125ActiveNot renewedNot yet renewed
MVP
MVP

Re: Contract status and renewal

I think I've overcomplicated things before, you don't need the aggregation and join step.

LOAD ContractID,

    StartDate,

    EndDate,

    [S/N],

    [Contract status]

//    [Contract renewal status] as Check2,

//    Expected

FROM

[https://community.qlik.com/thread/172582]

(html, codepage is 1252, embedded labels, table is @2);

LOAD ContractID,

  [S/N],

  StartDate,

  EndDate,

  [Contract status],

  if(peek([S/N]) <> [S/N], if([Contract status]<>'Expired', 'Not yet renewed','Not renewed'),'Renewed') as [Contract renewal status]

//  Expected,

//  Check2

Resident INPUT

ORDER BY [S/N], ContractID desc;

DROP TABLE INPUT;

agaetisproject
New Contributor III

Re: Contract status and renewal

Excellent, thank you Swuehl for your precious help and prompt answers.

You really made my day and learnt me how to use the very useful peek function!

Note, I sort the table on S/N, then EndDate as the source file had some inconsistencies in the attribution of ContractID. Minor adjustment for those out there that may use this answer.

Cheers,

Not applicable

Re: Contract status and renewal

Thank you Jeff and Swuehl. This is really helpful. Can anyone please help me calculate renewal rate KPI based on this? Greatly appreciate the work of posting this.

Thank you,

Parth

Not applicable

Re: Contract status and renewal

Hi Swuehl,

Thank you for the solution. I am also trying to create this new field for Contract Renewal status. The above mentioned solution worked perfectly for most cases. Because the data we have for the contracts; only places I saw different result than expected are below:

Scenario 1:

contract_idstart_dateend_dateserial_numberstatusrenewal_statusexpected result of renewal_status
1
12/23/201512/22/2018101ActiveNot yet renewed Renewed
212/23/201812/22/2020101FutureRenewed Not yet renewed
312/23/201512/22/2018102ActiveNot yet renewed Renewed
412/23/201812/22/2020102FutureRenewed Not yet renewed
51/1/201512/31/2017103ActiveNot yet renewed Renewed
61/1/201812/31/2019103FutureRenewed Not yet renewed
71/1/201512/31/2017104ActiveNot yet renewed Renewed
81/1/201812/31/2019104FutureRenewedNot yet renewed

Scenario 2:

contract_idstart_dateend_dateserial_numberstatusrenewal_statusexpected result of renewal_status
2010/9/201410/8/2015115ExpiredRenewed
2110/9/20159/30/2016115ActiveNot yet renewed
222/1/20152/1/2016116ExpiredNot renewed Renewed
232/1/20169/30/2016116ActiveRenewedNot yet renewed

I'd greatly appreciate any help you can provide.

Thank you,

Parth Shah

MVP
MVP

Re: Contract status and renewal

I would suggest that you create a new thread for your issue.

- Add a test script with some sample records

- Define which rules apply to your renewal status

Community Browser