Filter based on Date and type of state

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,

Input.xlsx

Datasets

Accepted answers

RobertOdera

Hi @vchauhan011

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

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

FilterBased_DateTypeState.yxmd

All comments

KP_DML

Hello,

Please see attached workflow.

States.yxmd

AndrewS

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

FilterOnDate.yxmd

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!

Input.xlsx

RobertOdera

Hi @vchauhan011

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

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

FilterBased_DateTypeState.yxmd

vchauhan011

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703