create start date and end date based on single date

[tharanikannan]

Hi all,

My fact table is like this,

Name	Jobid	Date	Status
ram	11	1/10/2021	submitted to sales
ram	11	1/10/2021	submitted to client
ram	11	2/10/2021	waiting for feedback from client
ram	11	4/10/2021	interview scheduled
ram	11	6/10/2021	interview happened
ram	11	9/10/2021	waiting for interview feedback

I need to apply interval match concept to being the status over a period of time. First step would be bringing start date and end date.

my result should be somethis like this,

Name	Jobid	startdate	enddate	Status
ram	11	1/10/2021	1/10/2021	submitted to sales
ram	11	1/10/2021	1/10/2021	submitted to client
ram	11	2/10/2021	3/10/2021	waiting for feedback from client
ram	11	4/10/2021	5/10/2021	interview scheduled
ram	11	6/10/2021	8/10/2021	interview happened
ram	11	9/10/2021	today()	waiting for interview feedback

Thanks,

@sunny_talwar 

[anthonyj]

Hi @tharanikannan ,

This code will build a start and end date based on your data. I assume the start and end date would be grouped by the Jobid so I've written to reset the start and end if the Jobid changes.

data:
load * Inline [

Name Jobid Date Status
ram 11 1/10/2021 submitted to sales
ram 11 1/10/2021 submitted to client
ram 11 2/10/2021 waiting for feedback from client
ram 11 4/10/2021 interview scheduled
ram 11 6/10/2021 interview happened
ram 11 9/10/2021 waiting for interview feedback
ram 12 1/10/2021 submitted to sales
ram 12 1/10/2021 submitted to client
ram 12 2/10/2021 waiting for feedback from client
ram 12 4/10/2021 interview scheduled
ram 12 6/10/2021 interview happened
ram 12 9/10/2021 waiting for interview feedback
](delimiter is '\t');

Output:
load
Name,
Jobid,
Status,
Date as StartDate,
if(Jobid <> peek(Jobid), date(today()),
peek('StartDate')) as EndDate
Resident data
order by Jobid, Date desc;

drop table data;

I hope this helps.

Regards

Anthony