Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DeeptiRao
Contributor II
Contributor II

Get Valid contract details

Hi All,

I have two table contract and revenue and also mentioned the output I need.

Contract table has contract for equipment with valid start and end date. Revenue table has the details about when the equipment was repaired and charges against them.

Input 1 : Contract Data 
EquipmentContractStartEnd
1C00112/17/201812/17/2019
1C00212/18/201912/17/2020
1C00312/18/202012/18/2021
2C0046/17/20186/17/2019
2C0056/6/20206/6/2021
4C0061/15/20181/15/2019

 

Input 2 : Revenue Data 
EquipmentPosting DateRevenue
19/8/2018284
17/5/2019220
12/6/2020203
13/28/2021296
24/28/2018209
27/2/2019281
27/26/2020283
39/8/2018289
37/5/2019298
411/26/2017257
43/6/2018221
43/6/2019252

 

Output :     
EquipmentPosting DateRevenueFlagContract ID
19/8/2018284Not in Contract 
17/5/2019220In ContractC001
12/6/2020203In ContractC002
13/28/2021296In ContractC003
24/28/2018209Not in Contract 
27/2/2019281Not in Contract 
27/26/2020283In ContractC005
39/8/2018289Not in Contract 
37/5/2019298Not in Contract 
411/26/2017257Not in Contract 
43/6/2018221In ContractC006
43/6/2019252Not in Contract 

 

The problem is to get details 'Not in Contract' where the Posting_date is not in the Start Date and End Date Range.

Kindly help to resolve this. Appreciate your help in advance.

1 Solution

Accepted Solutions
StarinieriG
Partner - Specialist
Partner - Specialist

Hi, 

you could use intervalmatch (file attached)

View solution in original post

2 Replies
StarinieriG
Partner - Specialist
Partner - Specialist

Hi, 

you could use intervalmatch (file attached)

DeeptiRao
Contributor II
Contributor II
Author

Thanks a lot @StarinieriG. The solution worked.