topic Re: Calculate year between dates in QlikView

Calculate year between dates

paulwalker — Sat, 16 Nov 2024 00:56:51 GMT

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!

Re: Calculate year between dates

Vegar — Mon, 30 Mar 2020 15:11:26 GMT

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.

Re: Calculate year between dates

paulwalker — Tue, 31 Mar 2020 04:29:55 GMT

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!

Re: Calculate year between dates

alex00321 — Tue, 31 Mar 2020 08:33:54 GMT

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!

Re: Calculate year between dates

Vegar — Tue, 31 Mar 2020 10:57:15 GMT

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;

Re: Calculate year between dates

alex00321 — Wed, 01 Apr 2020 07:28:37 GMT

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!

Re: Calculate year between dates

Vegar — Wed, 01 Apr 2020 09:16:37 GMT

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)

Re: Calculate year between dates

paulwalker — Wed, 08 Apr 2020 07:30:46 GMT

Thanks Alex! - anyhow I have achieved without interval match