Don't forget to register for our panel discussion with Dean Stoecker, Amy Holland, and Mark Frisch occurring next Wednesday, June 1!

2022-05-26 Updates: Email: If you're not seeing emails be delivered from the Community, please check your spam and mark the Community emails as not junk. Thank you for your patience.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Update/Replace column values if the column contains certain value

mjh418
8 - Asteroid

Hi All,

 

Would greatly appreciate if you can help me with the simplest solution for below:

 

There are items that belong to the Main Category (e.g. 100 and 200) such as XXX and AAA. There are also SUB-category items that share same main category code but have different sub-category codes. I want to replace/update the [Status] of the sub-items that belong in the same Main Category column with a "yes" IF there is YES in the status of the main category items. IF not Yes, then they can remain as Nulls. Please see below for sample and DESIRED result:

 

Sample:

DateItemMain CategorySub CategoryStatus
JanXXX100100YES
JanXAA100110[Null]
JanXBB100120[Null]
JanYYY200200NO
JanYAA200210[Null]
JanYBB200230[Null]
Feb............

 

Desired Result:

 

DateItemMain CategorySub CategoryStatus
JanXXX100100YES
JanXAA100110YES
JanXBB100120YES
JanYYY200200NO
JanYAA200210[Null]
JanYBB200230[Null]
Feb............

 

Thank you so much for your time and help.

Let me know if need clarification.

6 REPLIES 6
atcodedog05
22 - Nova
22 - Nova

Hi @mjh418 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1633950730052.png

 

Hope this helps : )

mjh418
8 - Asteroid

Hi @atcodedog05 ,

 

You are always so helpful, thank you.

But would this work if there are other months(dates) that should be treated separately? For example, in January 100 might be YES, but in February it might not be. 

Nanoq
8 - Asteroid

Here you go, you can use a multi row formula to do your logical check, just make sure that the top row is the one identifying the main category

if you need to treat months separately, just use the group my feature, and check off "date"
Let me know if this does what you want

Nanoq_0-1633951027553.png

 

atcodedog05
22 - Nova
22 - Nova

Hi @mjh418 

 

Here is the updated solution. You would need to Date also in the groupby and joining.

 

Workflow:

 

atcodedog05_0-1633951168026.png

 

Hope this helps : )

 

mjh418
8 - Asteroid

Thank you @Nanoq for the help! However, my input is very dynamic and cannot be guaranteed that the first row will be the main category. Thank you anyway, I got the solution!

mjh418
8 - Asteroid

@atcodedog05 Fantastic! Thank you very much for the help.

Labels