Populate value in a column based on condition

[srk0609]

I have a column valid/invalid with values "valid", "invalid". I want to create a new column Acct Valid/Invalid using following rule if there is even single invalid value for any row of Account number in existing column Valid/Invalid then all rows of Account Number should be coming up as "Invalid". I highlighted the column that needs to be created. Please let me know the steps. Thank you

[binuacs]

@srk0609 

[Qiu · Accepted solution]

@srk0609 
I would like to use the Boolyn value based on group of "Account Number" and "Period".

[jbichachi003]

Here's another solution!

This solution assumes that all the records are grouped correctly at the outset. If not, you'll have to first filter it to use this solution.

[TUSHAR050392]

@srk0609 Another solution here- Just follow below 2 steps.

1. Use a summarize tool and group by Account Number, Period and use Longest in the string option for Acct Valid/Invalid column (this will work since Invalid is longer in length than Valid and you have only 2 values - Valid & Invalid)

2. Join summarized output back to the main data on Account Number & Period. Drop the Acct Valid/Invalid, right account number, right period columns and change the name of Longest_Acct Valid/Invalid column to Acct Valid/Invalid.

[srk0609]

Thanks Qiu for your help. I made some changes to the workflow you provided to get the output I wanted I used max of flag instead of Sum as your solution was working only for instances where there is only one instance of invalid for Account number but I have instances where there are more than 1 instances of invalid for an Account.

[Qiu]

@srk0609 
Glad it helps