Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
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?
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.