How to add Data ranges based on present data

[MColeman1999]

I am not sure of a way I can add rows to my table for the times when someone is not in training from the time they are hired. See below for what my current output is and what I want.

EMPLOYEE_ID	Name_LastFirst	TrainingStart	TrainingEnd	TrainingType
12345	Doe, John	10/25/2021	1/24/2022	New Hire
12345	Doe, John	3/12/2022	6/11/2023	Transfer
12345	Doe, John	8/21/2023	11/19/2023	Transfer

EMPLOYEE_ID	Name_LastFirst	TrainingStart	TrainingEnd	TrainingType
12345	Doe, John	10/25/2021	1/24/2022	New Hire
12345	Doe, John	1/25/2023	3/11/2023	No Training
12345	Doe, John	3/12/2022	6/11/2023	Transfer
12345	Doe, John	6/12/2023	8/20/2023	No Training
12345	Doe, John	8/21/2023	11/19/2023	Transfer
12345	Doe, John	11/20/2023	12/4/2023 (Today)	No Training

[Gabbar]

Try this:-

MainTable:
Load EMPLOYEE_ID,Name_LastFirst,Date#(TrainingStart,'MM/DD/YYYY') as TrainingStart
,Date#(TrainingEnd,'MM/DD/YYYY') as TrainingEnd,TrainingType;
Load * Inline [
EMPLOYEE_ID,Name_LastFirst,TrainingStart,TrainingEnd,TrainingType
12345,DoeJohn,10/25/2021,1/24/2022,New Hire
12345,DoeJohn,3/12/2022,6/11/2023,Transfer
12345,DoeJohn,8/21/2023,11/19/2023,Transfer
];
Concatenate(MainTable)
Load EMPLOYEE_ID,Name_LastFirst,Date(TrainingEnd+1,'MM/DD/YYYY') as TrainingStart,
If(EMPLOYEE_ID=Previous(EMPLOYEE_ID),Date(Previous(TrainingStart)-1,'MM/DD/YYYY'),Date(today(),'MM/DD/YYYY')) as TrainingEnd,
'No Training' as TrainingType Resident MainTable
Order by EMPLOYEE_ID asc, TrainingEnd desc;

[Ahidhar]

try this

tab:
load Employee_ID,Name_LastFirst,date#(TrainingStart,'MM/DD/YYYY') as TrainingStart,date#(TrainingEnd,'MM/DD/YYYY') as TrainingEnd,TrainingType;
load * inline
[
Employee_ID,Name_LastFirst,TrainingStart,TrainingEnd,TrainingType
12345,'Doe,John',10/25/2021,01/24/2022,New Hire
12345,'Doe,John',03/12/2022,06/11/2023,Transfer
12345,'Doe,John',08/21/2023,11/19/2023,Transfer
];
Concatenate
load
Employee_ID,
Name_LastFirst,
date(peek('TrainingStart',RecNo(),'tab')-1,'MM/DD/YYYY') as TrainingEnd,
Date(TrainingEnd+1,'MM/DD/YYYY') as TrainingStart,
'No Training' as TrainingType
resident tab;
noConcatenate
tab1:
load
Employee_ID,Name_LastFirst,TrainingStart,
if(TrainingEnd>TrainingStart,TrainingEnd,date(Today(),'MM/DD/YYYY')) as TrainingEnd,
TrainingType
resident tab
order by TrainingStart,TrainingEnd;
drop Table tab;