single date column to start and finish date columns

[rrrggg20]

should be simple, but my mind is locked lately.

I have a table

Table

Load * inline [

task, date

task1, 1/1/2020

Miltone 1, 2/1/2021

task3, 5/1/2020

task4, 3/1/2020

milestone 2, 3/1/2022

AnothertaskmilstneUnrelated1, 2/3/2024

AnothertaskmilstoneUnrelated2/3/2020

Milestn 4, 3/1/2023

what I am trying to do is pull out the milestone dates and create a start/finish type layout.

note the name is not always "Milestone" so wildmatch('Milestone','') does not work

I am using Match(Task,'Miltone 1','Milestone 2', 'Milestn 4','AnothertaskmilstneUnrelated1','AnothertaskmilstoneUnrelated2')

to pull all the milestones, but what I need is to determine what activities fall between certain milestones

in this case

I need to create start and finishes between the Miltone 1, Milestone 2 and Milestn 4,but not the "unrelated"milestones

what I am hoping for is this

Th

Task	Start	End
Miltone 1	2/1/2021	2/1/2022	(1 Month before Milestone 2 starts
Milestone 2	3/1/2022	2/1/2023	1 month before milestone 4
Milestone 3			needs to b e here even if blank
Milestn 4	3/1/2023		no end date

[QFabian]

Hi @rrrggg20  please check this script, this is how it ends :

Table:
Load * inline [
task, date
task1, 1/1/2020
Miltone 1, 2/1/2021
task3, 5/1/2020
task4, 3/1/2020
milestone 2, 3/1/2022
AnothertaskmilstneUnrelated1, 2/3/2024
AnothertaskmilstoneUnrelated,2/3/2020
Milestn 4, 3/1/2023];

Aux:
Load
KeepChar(task, '1234567890') as Id,
date as Date
Resident Table
Where
upper(task) like 'MIL*';

drop table Table;

Milestone:
Load
Id,
Date as Start,
addmonths(previous(Date),-1) as End
Resident Aux
order by Id desc;

drop table Aux;

MaxId:
Load
min(Id) as MinId,
max(Id) as MaxId
Resident Milestone;

Let vMaxId = peek('MaxId', 0, 'MaxId');
Let vMinId = peek('MinId', 0, 'MaxId');

For vId = $(vMinId) to $(vMaxId)
Ids:
Load
$(vId) as Ids
Autogenerate(1);
Next
drop table MaxId;

concatenate(Milestone)
Load
Ids as Id
Resident Ids
where not exists(Id, Ids);

drop table Ids;

[RogerG]

sorry I lost access to that account, I would like to ask an admin to accept QFabian's answer

[QFabian]

Thanks!, glad to hear it is was usefull