Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
dtate1959
New Contributor II

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

I have  table of values such this:

ChangeDateProjectNameState
1/1/2019Test1Active
1/1/2019Test2Concept
1/1/2019Test3Concept
1/6/2019Test1Active
1/8/2019Test2Active
1/10/2019Test1On Hold
1/12/2019Test3On Hold
1/12/2019Test2On Hold
1/13/2019Test1On Hold
1/16/2019Test1On Hold
1/16/2019Test2On Hold
1/16/2019Test3On Hold
1/18/2019Test3On Hold
1/20/2019Test2Active
1/17/2019Test1Active
2/1/2019Test3On Hold
2/15/2019Test3Active
2/24/2019Test3On Hold
3/1/2019Test3Active

 

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) ).

ChangeDateProjectNameStatePrev DateNext Date
1/10/2019Test1On Hold1/1/20191/17/2019

 

Thanks in Advance!!

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

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

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

clipboard_image_0.png

 

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;

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

4 Replies
Partner
Partner

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

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;

clipboard_image_0.png

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

Blog: WhereClause   Twitter: @treysmithdev
dtate1959
New Contributor II

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

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

ex.

ChangeDateProjectNameState
1/1/2019Test3Concept
1/12/2019Test3On Hold
1/16/2019Test3On Hold
1/18/2019Test3On Hold
2/1/2019Test3On Hold
2/15/2019Test3Active
2/24/2019Test3On Hold
3/1/2019Test3Active

 

Using the current script my table looks like this:

clipboard_image_0.png

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

 

 

Partner
Partner

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

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

clipboard_image_0.png

 

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;

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

dtate1959
New Contributor II

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

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