Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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;
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.
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
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;
Awesome, looks great!! Thanks so much!!!