topic Re: How to replace fields in Alteryx Designer Desktop Discussions

How to replace fields

salangc — Mon, 24 Jun 2019 08:37:12 GMT

Hi Experts

i'm having trouble with my the formula/tools in alteryx

Rule: All Department 70674 should be tagged Transfer to 70202 in Department Descr column but it seems that it created a new row hence duplicating the data

*Note: I had prior rule to tag the in Account column in Products column (i.e. Account 8321216 = Product )

Output

Department	Department Descr	Account	Product	Amount
70674	France	8321216	8399095	2726.4
70674	Transfer to 70202	8321216		2726.4
70674	France	8341308	8399095	360
70674	Transfer to 70202	8341308		360
70674	France	8341308	8399095	537.44
70674	Transfer to 70202	8341308		537.44
70674	France	8641708	8699991	2531.49
70674	Transfer to 70202	8641708		2531.49

Desired output

Department	Department Descr	Account	Product	Amount
70674	Transfer to 70202	8321216	8399095	2726.4
70674	Transfer to 70202	8341308	8399095	360
70674	Transfer to 70202	8341308	8399095	537.44
70674	Transfer to 70202	8641708	8699991	2531.49

Re: How to replace fields

afv2688 — Mon, 24 Jun 2019 09:03:49 GMT

Hello @salangc ,

You could edit the column Department Desc on the formula tool using the following expression:

IF [Department] = 70674 THEN "Transfer to 70202" ELSE [Department Descr] ENDIF

In case the department was not a number but a string it would be like this:

IF [Department] = "70674" THEN "Transfer to 70202" ELSE [Department Descr] ENDIF

Cheers

Re: How to replace fields

danilang — Mon, 24 Jun 2019 11:23:20 GMT

Hi @salangc

The Formula tool in Alteryx can not add rows to your data. It can only add new columns or modify existing ones. The duplicate data is either present in the input data or is created as the result of join or union. Check the outputs of the tools before these to find out where the data is being duplicated. It's better to never produce the duplicates in the first place than to have to correct them.

If the data is in this form directly in the input, and you need to correct it, use a Filter to extract all rows where the Product is not empty then apply a Formula tool to change all the [Department Descr] to "Transfer to 70202". You may need to clean the Product column, because it seems to have a space which would count as not empty

Dan