How to replace fields

[salangc]

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

[afv2688]

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

[danilang · Accepted solution]

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