Get Valid contract details

DeeptiRao — Fri, 06 Sep 2019 12:53:45 GMT

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
Equipment	Contract	Start	End
1	C001	12/17/2018	12/17/2019
1	C002	12/18/2019	12/17/2020
1	C003	12/18/2020	12/18/2021
2	C004	6/17/2018	6/17/2019
2	C005	6/6/2020	6/6/2021
4	C006	1/15/2018	1/15/2019

Input 2 : Revenue Data
Equipment	Posting Date	Revenue
1	9/8/2018	284
1	7/5/2019	220
1	2/6/2020	203
1	3/28/2021	296
2	4/28/2018	209
2	7/2/2019	281
2	7/26/2020	283
3	9/8/2018	289
3	7/5/2019	298
4	11/26/2017	257
4	3/6/2018	221
4	3/6/2019	252

Output :
Equipment	Posting Date	Revenue	Flag	Contract ID
1	9/8/2018	284	Not in Contract
1	7/5/2019	220	In Contract	C001
1	2/6/2020	203	In Contract	C002
1	3/28/2021	296	In Contract	C003
2	4/28/2018	209	Not in Contract
2	7/2/2019	281	Not in Contract
2	7/26/2020	283	In Contract	C005
3	9/8/2018	289	Not in Contract
3	7/5/2019	298	Not in Contract
4	11/26/2017	257	Not in Contract
4	3/6/2018	221	In Contract	C006
4	3/6/2019	252	Not 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.

Re: Get Valid contract details

StarinieriG — Fri, 06 Sep 2019 13:23:08 GMT

Hi,

you could use intervalmatch (file attached)

Re: Get Valid contract details

DeeptiRao — Mon, 09 Sep 2019 06:51:11 GMT

Thanks a lot @StarinieriG. The solution worked.

topic Re: Get Valid contract details in QlikView

Get Valid contract details

Re: Get Valid contract details

Re: Get Valid contract details