Classify a customer as valid/invalid based on Status

[Ravireddy]

My requirement is to classify customer id into valid/invalid based on the status. I have two status OPEN and CLOSE. For any customer if the two consecutive status is same then it is invalid.

For example: Open, Close-Valid

Open Close Close - Invalid

so in the below example only first customer id a123 is valid and remaining two are invalid. Can anyone please help with a solution to solve this requirement.

Customer ID	Status
a123	Open
a123	Close
a123	Open
x345	Open
x345	Open
x345	Close
x345	Close
b112	Open
b112	Open
b112	Close

[BenMoss · Accepted solution]

@Ravireddy there's a few ways to approach this.

You could use the multirow formula tool, or perform a concatenation and search of the string.

The workflow attached shows an example of the latter.

Ben

[Kenda · Accepted solution]

Hey @Ravireddy

Here is an example using the Multi-Row Formula tool to solve your problem. If you need all 10 rows in the output, just join by Customer ID. See the attached workflow.

[Ravireddy · Accepted solution]

Thanks a lot @BarnesK