Filter based on Date and type of state

[vchauhan011]

Hello,

I have a dataset which look something like table below.

The output I am trying to get is as following. For each ID if the latest State transitioned to is either A, K, C or W, than take only latest date transitioned out of State and latest State transition To for that ID. If the latest State transitioned to is not A, K, C or W then eliminate all the entries for that ID from the output.

The output for that table should look something like table below. 

 

 

Please let me know if my question is not clear.

Thank you very much in Advance! Really appreciate your help. 

Best,

 

 

[KP_DML]

Hello,

Please see attached workflow.

[AndrewS]

Hi @vchauhan011 , similar as above, but I used a dateTime conversion to get the max date.

[vchauhan011]

Hello @KP_DML and @AndrewS,

first of all, thank you for posting both solutions so quickly. really appreciate it.  

Both solution are working perfectly fine in case if ID 2 has not been transitioned to or from A, K ,C or W states. But if ID 2 has been transitioned to and from A, K, C and W states and one of them is not the last state that ID 2 transitioned to, this would still pick ID 2 with the last date. 

 

for any given ID If the very last state it transitioned to is not A, W, K or C then all the entries for that ID should be removed. 

Hope this make sense to you. 

Thank you again! 

 

[RobertOdera · Accepted solution]

Hi @vchauhan011 

Kindly try the below and let me know if it works for you!

Please let me know if you need anything else...

[vchauhan011]

Hello @RobertOdera, thank you for sharing that solution with me. It worked! Appreciate your help! 

[RobertOdera]

@vchauhan011  you're most welcome.

Please don't hesitate to ask the community if you need anything else!