Announcements
cancel
Showing results for
Did you mean:
Creator III

## Calculate year between dates

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

Labels (1)
• ### NEW QLIKVIEW

1 Solution

Accepted Solutions
Creator II

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

``````ABC:
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:
\$(_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:
ID,
[Start Year]&[End Year] as key,
[Start Date],
[End Date],
Rate
Resident ABC;

DROP Table ABC;``````

Thanks!

7 Replies
MVP

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.

Creator III
Author

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:
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:
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 )

Resident OrderLog;

DROP Table ABC;

Thanks!

Creator II

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!

MVP

here is an example using intervalmatch

``````ABC:
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())
\$(_year) as Year
AutoGenerate 1;
next _year

Left join IntervalMatch (Year)
Resident ABC;``````

Creator II

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

``````ABC:
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:
\$(_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:
ID,
[Start Year]&[End Year] as key,
[Start Date],
[End Date],
Rate
Resident ABC;

DROP Table ABC;``````

Thanks!

MVP

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)

Creator III
Author

Thanks Alex! - anyhow I have achieved without interval match

Community Browser