Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Flag records which have Gaps in support contract

Hello all,

I am currently working on a report in which I would like to flag those records which have gaps in their support contract.

One asset has more than one support contract records associated. Capture.JPG

I would like to flag those records in which there is a gap in the end date of previous record and start date of the current record.

For example, if one contract expired at 12/13/2014 and next contract of the same asset kicks in at 12/15/2014, than I would like to flag it.

Please let me know how to achieve it. Any help is appreciated.

Thank you,

Parth

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Perhaps like this?

LEFT JOIN (Contract)
LOAD
asset_id
,contract_id
,if(asset_id = previous(asset_id) and contract_start_date <> previous(contract_end_date) + 1,'Y','N') as contract_gap?

RESIDENT Contract

ORDER BY

asset_id

,contract_id

,contract_start_date

Edit: If you can do the order by when you first read in the data, it may be better to add the field there rather than do a left join.

View solution in original post

2 Replies
Highlighted
MVP
MVP

Perhaps like this?

LEFT JOIN (Contract)
LOAD
asset_id
,contract_id
,if(asset_id = previous(asset_id) and contract_start_date <> previous(contract_end_date) + 1,'Y','N') as contract_gap?

RESIDENT Contract

ORDER BY

asset_id

,contract_id

,contract_start_date

Edit: If you can do the order by when you first read in the data, it may be better to add the field there rather than do a left join.

View solution in original post

Highlighted
Not applicable

Thank you John.

Regards,

Parth