Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Replacing values

bdsibert
6 - Meteoroid

I have a dataset with column SF_PLAN_ENTITY_CD where the values are either 5, 6, 7, 8, 9, or 10. I also have columns INC and BOARD where the values are either Y or N. I am looking to scan the dataset and if the SF_PLAN_ENTITY_CD = 5 AND INC = Y AND BOARD = N, then replace the 5 with a 7. If the filters are not met, SF_PLAN_ENTITY_CD should remain the same.

 

So for example,

 

SF_PLAN_ENTITY_CD  | INC | BOARD

5                                        Y        N            --------> SF_PLAN_ENTITY_CD is replaced with 7

6                                        Y        N            --------> SF_PLAN_ENTITY_CD remains a 6

5                                        N        N            --------> SF_PLAN_ENTITY_CD remains a 5

 

This was the initial thought that I had, but I am only returning zeroes in my output file.

 

IF ([SF_PLAN_ENTITY_CD]=="5" AND [INC]=="Y" AND [BOARD]=="N") THEN [SF_PLAN_ENTITY_CD]=="7" ELSE [SF_PLAN_ENTITY_CD]=="5" ENDIF

 

Any thoughts would be greatly appreciated!

7 REPLIES 7
DataBlender
11 - Bolide

Hi @bdsibert, you can use the formula to update the SF_PLAN_ENTITY_CD field.

 

Try the below (which assumes that SF_PLAN_ENTITY_CD is a numeric field type, if not, surround the 5 in inverted commas):

 

IIF(
[SF_PLAN_ENTITY_CD]=5 AND
[INC] = 'Y' AND
[BOARD] = 'N',
7,
[SF_PLAN_ENTITY_CD])

bdsibert
6 - Meteoroid

I must say I absolutely love this community. That is exactly what I was looking for @DataBlender and it works perfectly! I was making things too complicated with the IF statement, so the Boolean is much better. Thank you again!

MarqueeCrew
20 - Arcturus
20 - Arcturus

@bdsibert,

 

Leading the community are @JulieH and @BrianO.  They won't take credit for all of the work that their team provides.  They celebrate their team efforts and of course the participation of folks like @DataBlender who volunteer their help in serving the alteryx community.  Soon, I hope to see you earn a badge for providing a solution yourself.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
BrianO
Alteryx Alumni (Retired)

@bdsibert wrote:

I must say I absolutely love this community.


❤️

Andy_Katona
8 - Asteroid

Does the same work if I need to replace several values? I need to take the values below & make then Blank.

 

E.G

 

IF [Proc]=="N/C"

    OR [Proc]=="NOCHG" 

    OR [Proc]=="MISC" 
THEN [Proc]== Null()
ELSE [Proc]
ENDIF

DataBlender
11 - Bolide

The same logic can be applied, but you would require a slight tweak to your example. In a formula tool, you would choose to update the Proc column and have the following expression:

 

IF [Proc]=="N/C"
    OR [Proc]=="NOCHG" 
    OR [Proc]=="MISC" 
THEN Null()
ELSE [Proc]
ENDIF

Note how the then clause is slightly different. There are other options available which may be more efficient depending on how many values you need to substitute. You could also use:

IF [Proc] IN ("N/C", "NOCHG", "MISC")
THEN Null()
ELSE [Proc]
ENDIF

If you have a longer list you might want to use the Find Replace tool to replace specified values with null

 

Andy_Katona
8 - Asteroid

Thank you!!!!!!!!!

Labels