Assign a date period based on 2 dates

[robin_heijt]

Hi,

My QS app is currently build on approximately 10 different data files, which are linked together through joins.

The main identifier in these files are employees linking all different files together through a unique identifier number.

Each employee has a start and end date (In case of no end date, it is mapped as Today).

Based on these dates, I want to create a date field that shows in which Year/Month the employee was or is active.

With the help of @sunny_talwar I have been able to identify a solution. However with approximately 7000 Employees in my files, the following piece of code is taking more than 15 hours when joined in the general script eventually leading to the script running into a run-time error.

When adding the code as a separate entity, and linking it together with the Unique identifier, it takes only 1 hour and it succeeds.

The reason I need this code added to the joined script, is because I need to write an IF Statement that involves fields from different files.

I am now hoping somebody can help me figure out a way, how I can use this code with the fields from the joined script. So I can write the IF Statement with fields from different files.

Load*,

YearName(ActiveDate, 0, 7) as "ActiveCycle";

LOAD *,
  Date("Hiring Date" + IterNo() - 1) as ActiveDate
//   MonthName("Hiring Date" + IterNo() - 1) as ActiveMonthYear,
//  Year(Date("Hiring Date" + IterNo() - 1)) as ActiveYear
While "Hiring Date" + IterNo() - 1 <= Alt("Leaving Date", Today());
load*,
alt(Date(Date#([Termination Date], 'DD.MM.YYYY')), date(today())) AS [Leaving Date];

Load*,
if(S='3',date(today()),[Leaving Da]) as "Termination Date";

LOAD
	[Global ID],
	Date(Date#([Hiring Dat], 'DD.MM.YYYY') ) AS [Hiring Date],
    "Leaving Da",
    S
    
FROM [lib://Qlik_People_Reporting (one_c403515)/Employee Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thanks!

[robin_heijt]

Hi,

Thanks again for all your input.

So I have finally been able to get everything to work, but I am still experiencing the issue of duplicate lines.

As you can see in the picture,  I need separate lines to calculate an implementation.

In this scenario Jack already has the field as implemented, but because the cycle 2018/2019 is duplicate with both the movement type and the employed fact, it shows him as both implemented and not implemented.

I use the following expression:

= If([ActiveCycle]='2018-2019' and "OPR 2018"='4A',
 IF("Fact_Type"='Promotion Band Up','Implemented' ,
 IF("Fact_Type"='Promotion Within Band','Not Implemented' ,
 IF("Fact_Type"='Lateral Move','Not Implemented' ,
 IF(isnull("Fact_Type"), 'Not Implemented' ,
 IF("Fact_Type"='Termination Voluntary','Not Implemented',
 IF("Fact_Type"='Termination Involuntary', 'Not Implemented','Not Implemented')))))) )

Can you advice on how to fix this?

Thanks a lot!