topic Re: Replacing values in Alteryx Designer Desktop Discussions

Replacing values

bdsibert — Thu, 24 May 2018 02:48:04 GMT

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!

Re: Replacing values

DataBlender — Wed, 08 Feb 2017 16:07:40 GMT

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])

Re: Replacing values

bdsibert — Wed, 08 Feb 2017 16:29:53 GMT

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!

Re: Replacing values

MarqueeCrew — Wed, 08 Feb 2017 16:35:41 GMT

@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

Re: Replacing values

BrianO — Thu, 09 Feb 2017 17:12:08 GMT

@bdsibert wrote:

I must say I absolutely love this community.

❤️

Re: Replacing values

Andy_Katona — Tue, 28 Mar 2017 23:42:10 GMT

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

Re: Replacing values

DataBlender — Wed, 29 Mar 2017 13:55:17 GMT

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

Re: Replacing values

Andy_Katona — Wed, 29 Mar 2017 15:29:05 GMT

Thank you!!!!!!!!!