Calculate year between dates

[paulwalker]

HI Community,

My Scenario, I have to create Year based on Start and End date.

Example: If i Click Year=2019,  it should be filter data between start/end date.

ID	Start Date	End Date	Duration	Rate
100	Jan'19	Jan'20	Jan'19 - Jan'20	100
200	Jan'19	Jan'20	Jan'19 - Jan'20	200
300	Jan'19	Jan'20	Jan'19 - Jan'20	240
400	Jan'20	Sep'20	Jan'20 - Sep'20	2500
500	Jan'20	Oct'20	Jan'20 -  Oct'20	123
600	Jul'18	Oct'19	Jul'18 - Oct'19	250
700	Jul'18	Nov'20	Jul'18 - Nov'20	1100
800	Aug'17	Jul'20	Aug'17 - Jul'20	1000

OutPut Should be :

selection Year = 2019

Example 1:

ID	Start Date	End Date	Duration	Rate	Explain (Between Years)
100	Jan'19	Jan'20	Jan'19 - Jan'20	100	2019,2020
200	Jan'19	Jan'20	Jan'19 - Jan'20	200	2019,2020
300	Jan'19	Jan'20	Jan'19 - Jan'20	240	2019,2020
600	Jul'18	Oct'19	Jul'18 - Oct'19	250	2018,2019
800	Aug'17	Jul'20	Aug'17 - Jul'20	1000	2017,2018,2019,2020

Example 2: 

Selection Year = 2018

ID	Start Date	End Date	Duration	Rate	Out put Explain (Between Years)
600	Jul'18	Oct'19	Jul'18 - Oct'19	250	2018, 2019
700	Jul'18	Nov'20	Jul'18 - Nov'20	1100	2018, 2019,2020
800	Aug'17	Jul'20	Aug'17 - Jul'20	1000	2017, 2018, 2019, 2020

Thanks In Advance!

[Vegar]

Take a look at the consept of IntervalMatch in QlikView.  

You will find a lot of interval match examples in the community forum as well. 

[paulwalker]

Thanks for your suggestion !

Already have done with Interval match, but problem is my real data - have so many years, we can't do manual enter every time..

Is there any other solution ??

ABC:
LOAD * INLINE [
ID, Start Date, End Date, Years, Rate
100, "Jan'19", "Jan'20", 2019:2020, 100
200, "Jan'19", "Jan'20", 2019:2020, 200
300, "Jan'19", "Jan'20", 2019:2020, 240
400, "Jan'20", "Sep'20", 2020:2020, 2500
500, "Jan'20", "Oct'20", 2020:2020, 123
600, "Jul'18", "Oct'19", 2018:2019, 250
700, "Jul'18", "Nov'20", 2018:2020, 1100
800, "Aug'17", "Jul'20", 2017:2020, 1000
];

OrderLog:
LOAD * INLINE [
Start, End, Order
2019:2020, 2019:2020, 2019
2019:2020, 2019:2020, 2020
2020:2020, 2020:2020, 2020
2018:2019, 2018:2019, 2018
2018:2019, 2018:2019, 2019
2018:2020, 2018:2020, 2018
2018:2020, 2018:2020, 2019
2018:2020, 2018:2020, 2020
2017:2020, 2017:2020, 2017
2017:2020, 2017:2020, 2018
2017:2020, 2017:2020, 2019
2017:2020, 2017:2020, 2020
];

INNER JOIN IntervalMatch ( Years )

LOAD Start, End
Resident OrderLog;

DROP Table ABC;

Thanks!

[alex00321]

Hi you could try my attached file to see if this helps. The sample file in source is what you shared, I saved that into excel. Thanks!

[Vegar]

here is an example using intervalmatch

ABC:
LOAD 
ID, 
year(date#([Start Date], 'MMM'&chr(39)&'YY')) as [Start Year],
year(date#([End Date], 'MMM'&chr(39)&'YY')) as [End Year],
[Start Date],
[End Date],
Rate
 INLINE [
ID, Start Date, End Date, Years, Rate
100, "Jan'19", "Jan'20", 2019:2020, 100
200, "Jan'19", "Jan'20", 2019:2020, 200
300, "Jan'19", "Jan'20", 2019:2020, 240
400, "Jan'20", "Sep'20", 2020:2020, 2500
500, "Jan'20", "Oct'20", 2020:2020, 123
600, "Jul'18", "Oct'19", 2018:2019, 250
700, "Jul'18", "Nov'20", 2018:2020, 1100
800, "Aug'17", "Jul'20", 2017:2020, 1000
];

For _year = 2017 to year(today())
LOAD 
$(_year) as Year
AutoGenerate 1;
next _year

Left join IntervalMatch (Year)
LOAD [Start Year], [End Year]
Resident ABC;

[alex00321 · Accepted solution]

Thanks for this, but I got synthetic keys on your scripts, so I updated a little.

ABC:
LOAD 
ID, 
year(date#([Start Date], 'MMM'&chr(39)&'YY')) as [Start Year],
year(date#([End Date], 'MMM'&chr(39)&'YY')) as [End Year],
[Start Date],
[End Date],
Rate
 INLINE [
ID, Start Date, End Date, Years, Rate
100, "Jan'19", "Jan'20", 2019:2020, 100
200, "Jan'19", "Jan'20", 2019:2020, 200
300, "Jan'19", "Jan'20", 2019:2020, 240
400, "Jan'20", "Sep'20", 2020:2020, 2500
500, "Jan'20", "Oct'20", 2020:2020, 123
600, "Jul'18", "Oct'19", 2018:2019, 250
700, "Jul'18", "Nov'20", 2018:2020, 1100
800, "Aug'17", "Jul'20", 2017:2020, 1000
];

For _year = 2017 to year(today())
tblTempNew:
LOAD 
$(_year) as Year
AutoGenerate 1;
next _year

Left join IntervalMatch (Year)
LOAD [Start Year] , [End Year] 
Resident ABC;

tblNew:
LOAD Year, [Start Year] & [End Year] as key Resident tblTempNew;

DROP Table tblTempNew;

tblABC:
LOAD
ID, 
[Start Year]&[End Year] as key,
[Start Date],
[End Date],
Rate
Resident ABC;

DROP Table ABC;

Thanks!

[Vegar]

Thats great @alex00321 , I admit that it's looks prettier without the synthetic keys, but it will work correctly with or without.

(IntervalMatch is pretty much the only scenario where consider leaving the synthetic keys in my applications)

[paulwalker]

Thanks Alex! - anyhow I have achieved without interval match