topic Re: Include years between Start Date and End Date in QlikView

Include years between Start Date and End Date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have a Table stating student details.

Student 1d, Student Name, Start Date, End Date

1,A,'2012-04-01','2016-12-01'

2,B,'2017-01-03','2017-01-31'

3,C,'2016-04-13','2019-02-02'

I need to create a dropdown stating Year.

After year selection, I should only see students active in that year

for example :

student A should be active for year selection of 2012,2013,2014,2015,2016

student B should be active for year selection of 2017

student C should be active for year selection of 2016,2017,2018,2019

Kindly advise the formula used for creating the Year dropdown.

Re: Include years between Start Date and End Date

devarasu07 — Fri, 02 Feb 2018 17:27:29 GMT

HI,

Try like this

Temp:
load * Inline [
Student 1d, Student  Name, Start Date, End Date
  1,A,2012-04-01,2016-12-01
  2,B,2017-01-03,2017-01-31
  3,C,2016-04-13,2019-02-02];

Fact:
load *, Year(Date) as Year;
LOAD [Student 1d], [Student  Name],
Date([Start Date]+iterno()-1) as Date
resident Temp while [Start Date]+IterNo()-1 <=[End Date];

drop table Temp;

Re: Include years between Start Date and End Date

Anonymous — Mon, 05 Feb 2018 11:15:06 GMT

Thanks Devarasu,

It worked Exactly as I wanted it to be.... However I can see more latency in dashboard, as it takes more time to load my pivot every time I make any selections. Can you please suggest how that could be avoided?

Regards,

Sonal Sabhnani

Re: Include years between Start Date and End Date

devarasu07 — Mon, 05 Feb 2018 17:13:46 GMT

Hi,

Have you tried with Straight table instead of pivot table?(usually table/straight table is much faster than pivot ) stalwar1‌ can you share your thoughts on this issue. Thanks

Regards,
Deva

Re: Include years between Start Date and End Date

sunny_talwar — Mon, 05 Feb 2018 17:25:47 GMT

I believe the problem could be related to the number of rows that were present even before the while statement was implemented. If there were 1 million rows of data points in your fact table, implementing while could very easily multiple that data based on how many years each student are present. There is no easy way to handle this kind of situation... except for may be creating a link table with all the years, rather than using your fact table because fact table might have 30 fields, and it might not be a good idea to multiple all of them. Rather, just duplicate the key field and the new year field which might give somewhat better performance.

Re: Include years between Start Date and End Date

sunny_talwar — Mon, 05 Feb 2018 17:27:18 GMT

Also, IntervalMatch with a master calendar might be something else which can be tested