Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
paulwalker
Creator II
Creator II

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.

IDStart DateEnd DateDurationRate
100Jan'19Jan'20Jan'19 - Jan'20100
200Jan'19Jan'20Jan'19 - Jan'20200
300Jan'19Jan'20Jan'19 - Jan'20240
400Jan'20Sep'20Jan'20 - Sep'202500
500Jan'20Oct'20Jan'20 -  Oct'20123
600Jul'18Oct'19Jul'18 - Oct'19250
700Jul'18Nov'20Jul'18 - Nov'201100
800Aug'17Jul'20Aug'17 - Jul'201000

 

OutPut Should be :

selection Year = 2019

Example 1:

IDStart DateEnd DateDurationRateExplain (Between Years)
100Jan'19Jan'20Jan'19 - Jan'201002019,2020
200Jan'19Jan'20Jan'19 - Jan'202002019,2020
300Jan'19Jan'20Jan'19 - Jan'202402019,2020
600Jul'18Oct'19Jul'18 - Oct'192502018,2019
800Aug'17Jul'20Aug'17 - Jul'2010002017,2018,2019,2020

 

Example 2: 

Selection Year = 2018

IDStart DateEnd DateDurationRateOut put Explain (Between Years)
600Jul'18Oct'19Jul'18 - Oct'192502018, 2019
700Jul'18Nov'20Jul'18 - Nov'2011002018, 2019,2020
800Aug'17Jul'20Aug'17 - Jul'2010002017, 2018, 2019, 2020

 

Thanks In Advance!

Labels (1)
1 Solution

Accepted Solutions
alex00321
Creator II
Creator II

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!

View solution in original post

7 Replies
Vegar
MVP
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. 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
paulwalker
Creator II
Creator II
Author

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
Creator II
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!

Vegar
MVP
MVP

here is an example using intervalmatch

image.png

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;

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
alex00321
Creator II
Creator II

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!

View solution in original post

Vegar
MVP
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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
paulwalker
Creator II
Creator II
Author

Thanks Alex! - anyhow I have achieved without interval match