Creating new records depending on dates.

[BenSchenck]

Hi everyone,

I'm quite new to Qlik and have an issue where I couldn't find an answer for.

I have the following table:

Person

Subtype

NationalNumber

StartDate

EndDate

Seniority

Status

ContractStatus

Office

Person	Subtype	NationalNumber	StartDate	EndDate	Seniority	Status	ContractStatus	Office
X1	A	XXXXXXXXXXXX	14/03/2022	30/04/2022	47	Terminated	No active contract	K
X2	B	XXXXXXXXXXXX	12/11/2018		1864	Active	Active open-ended contract	L
X3	C	XXXXXXXXXXXX	13/10/2022	30/11/2022	48	Terminated	No active contract	M

Now I want to create a new record for every year end (31/12/20XX) between 2008-2022, add a new row with the year.
The goal is to calculate the Median/ Average seniority dates at the end of every year. 

expected result is this for person X2

Housekeeper	Subtype	NationalNumber	StartDate	EndDate	YearEnd	Year	Seniority	Status	ContractStatus	Office
X2	I	XXXXXXXXXXXX	12/11/2018		31/12/2018	2018	49	Active	Active open-ended contract	L
X2	I	XXXXXXXXXXXX	12/11/2018		31/12/2019	2019	414	Active	Active open-ended contract	L
X2	I	XXXXXXXXXXXX	12/11/2018		31/12/2020	2020	780	Active	Active open-ended contract	L
X2	I	XXXXXXXXXXXX	12/11/2018		31/12/2021	2021	1145	Active	Active open-ended contract	L
X2	I	XXXXXXXXXXXX	12/11/2018		31/12/2022	2022	1510	Active	Active open-ended contract	L
X2	I	XXXXXXXXXXXX	12/11/2018				1864	Active	Active open-ended contract	L

This for all people in the list either between their start & end date or latest 31/12/2022

It might be possible that person X2 is more then once on the list but not during the same timeframe. 

Thanks!

KR,

Ben 

[steeefan · Accepted solution]

My suggestion is to iterate over all rows, then for each Person generate as much rows you need using WHILE with IterNo().

Does this look like the result you need?

[Gabbar · Accepted solution]

Multiple ways to do this in script; you can use Iterno, Autogenerate, IntervalMatch, Cross join.

@steeefan  has already explained iterno and nearly same is case with autogenerate. 

In Script:-
Using IntervalMatch()  ,
You can create a new field where EndDate2 which is Coalesce(EndDate,Today()).
In  second table you can create only YearEnd Dates using calendar method.
After this you can Use IntervalMatch to join both Table;

Using Outerjoin:
Create YearEnd Date In Similar way as Last method and Outer Join/Cross join it on previous Table1.
The Create Table2 taking resident of Table 1 and Using this where Condition.
where YearEnd > StartDate and YearEnd <= Coalesce(EndDate,Today());

This is same way as interval match but as you are joining all Tables created in the end, You can choose your method depending on conditions.