How to autogenerate year from start date to end date by ID?

[Saif1]

Hello Qlikers ,

I have this following data as example :

EMP ID	Start Date	End Date
1	01/09/2008	01/01/2014
2	05/10/2017	01/10/2020
3	10/10/2018	01/10/2022
4	10/10/2019	01/09/2022

Start date is where the employee started working at company, End date is when he left that company,

my end goal is to get this table:

EMP ID	Start Date	End Date	Years	Is active flag
1	01/09/2018	01/01/2020	2018	Y
1	01/09/2018	01/01/2020	2019	Y
1	01/09/2018	01/01/2020	2020	Y
1	01/09/2018	01/01/2020	2021	N
1	01/09/2018	01/01/2020	2022	N
2	05/10/2017	01/01/2021	2017	y
2	05/10/2017	01/01/2021	2019	y
2	05/10/2017	01/01/2021	2020	y
2	05/10/2017	01/01/2021	2021	y
2	05/10/2017	01/01/2021	2022	N

and the rest of employees,

So, I want to generate the "Year" column and the "is active flag " from the load editor, the year is generated from the 

oldest Start Date (here is 2017) and to today's date.

any help to generate this?

really need the help , thank you in advance.

[justISO · Accepted solution]

Hi, not so elegant solution, but you can try something like this:

sample:
load * inline [
EMP ID,  	Start Date, 	End Date
1, 	01/09/2008, 	01/01/2014,
2, 	05/10/2017, 	01/10/2020,
3, 	10/10/2018, 	01/10/2022,
4, 	10/10/2019, 	01/09/2022,
];
years:
LOAD 
min(year([Start Date])) as Min_year
RESIDENT sample;

LET vMinYear=peek('Min_year','0','years');

JOIN (sample)
LOAD 
$(vMinYear) + RecNo()-1 AS Years 
AutoGenerate year(today())-$(vMinYear)+1;

final:
LOAD *,
if(year([End Date])<Years, 'N', 'Y') as [Is active flag]
RESIDENT sample
WHERE Years>=year([Start Date]);

DROP TABLES years, sample;

[Saif1 · Accepted solution]

This worked for me too , but thank you for the solution

Employee:

LOAD

    "EMP ID",

    "Start Date",

    "End Date",

    Department,

    "Section"

FROM [lib://AttachedFiles/Book 3.xlsx]

(ooxml, embedded labels, table is Sheet1);



Final:

  LOAD *,

  year(YearStart("Start Date",IterNo()-1)) as Year

  Resident Employee

  While YearStart("Start Date",IterNo()-1)<=if(floor(Year("End Date"))> Floor(Year(Today())),YearEnd(Today()),YearEnd("End Date"));




  Drop table Employee;

  flag:

  load*,

  if(year("End Date")>=Year,'ACTIVE','NOT ACTIVE') as flag,

  AutoNumber(RowNo(),"EMP ID"&Year) as rownum

  Resident Final

  Order by "Start Date" desc;