Need help finding the last and next date of a State change

[dtate1959]

I have  table of values such this:

ChangeDate	ProjectName	State
1/1/2019	Test1	Active
1/1/2019	Test2	Concept
1/1/2019	Test3	Concept
1/6/2019	Test1	Active
1/8/2019	Test2	Active
1/10/2019	Test1	On Hold
1/12/2019	Test3	On Hold
1/12/2019	Test2	On Hold
1/13/2019	Test1	On Hold
1/16/2019	Test1	On Hold
1/16/2019	Test2	On Hold
1/16/2019	Test3	On Hold
1/18/2019	Test3	On Hold
1/20/2019	Test2	Active
1/17/2019	Test1	Active
2/1/2019	Test3	On Hold
2/15/2019	Test3	Active
2/24/2019	Test3	On Hold
3/1/2019	Test3	Active

For each line I need to display the date the state last changed and the date the next change occurs. 

(example:  For ProjectName 'Test1',for  the entry  on date 1/10/2019  I want to find the date the State last changed (1/1/2019) and the date the next change occurred (1/17/2019) ).

ChangeDate	ProjectName	State	Prev Date	Next Date
1/10/2019	Test1	On Hold	1/1/2019	1/17/2019

Thanks in Advance!!

[treysmithdev]

I can't think of an efficient way to do this on the front end. However, below is how you can do it through the script.

data:
Load
	Date(Min(ChangeDate)) 	as EntryDate,
    Date(Max(ChangeDate)+1)	as NextDate,
    ProjectName,
    State
Group By	
	ProjectName,
    State;
Load * Inline [
ChangeDate	ProjectName	State
1/1/2019	Test1	Active
1/1/2019	Test2	Concept
1/1/2019	Test3	Concept
1/6/2019	Test1	Active
1/8/2019	Test2	Active
1/10/2019	Test1	On Hold
1/12/2019	Test3	On Hold
1/12/2019	Test2	On Hold
1/13/2019	Test1	On Hold
1/16/2019	Test1	On Hold
1/16/2019	Test2	On Hold
1/16/2019	Test3	On Hold
1/18/2019	Test3	On Hold
1/20/2019	Test2	Active
1/17/2019	Test1	Active
2/1/2019	Test3	On Hold
2/15/2019	Test3	Active
2/24/2019	Test3	On Hold
3/1/2019	Test3	Active
](delimiter is \t);



transformed:
Load
	ProjectName,
    State,
    EntryDate,
    NextDate,
	Date(if(ProjectName = Previous(ProjectName), Previous(EntryDate), Peek(PrevDate))) as PrevDate
Resident
	data
Order by
	ProjectName,
    EntryDate;
    

final:
NoConcatenate Load
	ProjectName,
    State,
    EntryDate,
    Date(if(ProjectName = Previous(ProjectName) AND State <> Previous(State), Peek(EntryDate),Alt(Peek(NextDate),NextDate))) as NextDate,
	PrevDate
Resident
	transformed
Order by
	ProjectName,
    EntryDate desc;
    
    
drop tables data,transformed;

If you want to only store the latest or just show the latest on the front there are several ways.  

[dtate1959]

This is looking good except that a state can change  back to a previous state, 

ex.

ChangeDate	ProjectName	State
1/1/2019	Test3	Concept
1/12/2019	Test3	On Hold
1/16/2019	Test3	On Hold
1/18/2019	Test3	On Hold
2/1/2019	Test3	On Hold
2/15/2019	Test3	Active
2/24/2019	Test3	On Hold
3/1/2019	Test3	Active

Using the current script my table looks like this:

I believe the Group By ProjectName, State is not allowing the ChangeDate entries on 2/24/2019 and 3/1/2019 to display in the Table.

Any thoughts on how to resolve this and basically show a history of the changes sorted by the EntryDate??

Thanks,

Dave T

[treysmithdev · Accepted solution]

You're right. Also, the ProjectName was taking the EntryDate from the previous test. I fixed that as well. 

data:
Load * Inline [
ChangeDate	ProjectName	State
1/1/2019	Test1	Active
1/1/2019	Test2	Concept
1/1/2019	Test3	Concept
1/6/2019	Test1	Active
1/8/2019	Test2	Active
1/10/2019	Test1	On Hold
1/12/2019	Test3	On Hold
1/12/2019	Test2	On Hold
1/13/2019	Test1	On Hold
1/16/2019	Test1	On Hold
1/16/2019	Test2	On Hold
1/16/2019	Test3	On Hold
1/18/2019	Test3	On Hold
1/20/2019	Test2	Active
1/17/2019	Test1	Active
2/1/2019	Test3	On Hold
2/15/2019	Test3	Active
2/24/2019	Test3	On Hold
3/1/2019	Test3	Active
](delimiter is \t);


order:
Load
	ProjectName,
    State,
    ChangeDate,
    If(RowNo() = 1, 1,If(ProjectName = Previous(ProjectName) AND State = Previous(State), Peek(StateID),Peek(StateID)+1)) as StateID
Resident
	data
Order by
	ProjectName,
    ChangeDate;
    
    
group:
Load
	Date(Min(ChangeDate)) 	as EntryDate,
    Date(Max(ChangeDate)+1)	as NextDate,
    ProjectName,
    StateID,
    State
resident 
	order
Group By	
	ProjectName,
    StateID,
    State;
    
 
transformed:
Load
	ProjectName,
    StateID,
    State,
    EntryDate,
    NextDate,
	Date(if(ProjectName = Previous(ProjectName) AND State <> Previous(State), Previous(EntryDate), If(ProjectName = Previous(ProjectName), Peek(PrevDate)))) as PrevDate
Resident
	group
Order by
	ProjectName,
    EntryDate,
    StateID;
    

final:
NoConcatenate Load
	ProjectName,
    StateID,
    State,
    EntryDate,
    Date(if(ProjectName = Previous(ProjectName) AND StateID <> Previous(StateID), Peek(EntryDate),If(ProjectName = Previous(ProjectName),Alt(Peek(NextDate),NextDate),NextDate))) as NextDate,
	PrevDate
Resident
	transformed
Order by
	ProjectName,
    StateID desc,
    EntryDate desc;
    
    
drop tables data,order, group, transformed;

[dtate1959]

Awesome, looks great!! Thanks so much!!!