Populate value in a column based on condition

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

Global_NotGlobal.xlsx

Workflow

Datasets

Tips and Tricks

Common Use Cases

Transformation

Accepted answers

Qiu

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

0831-srk0609.yxmd

All comments

binu_acs

@srk0609

Valid_Invalid_30082024.yxmd

Qiu

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

0831-srk0609.yxmd

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.

Alteryx Solution.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317