Double to String with formula tool

[Phill]

Hello, 

I'm trying to replace the numeric values in a column with a string value but I'm coming up against multiple issues. 

Basically my column (there actually 6 I'm trying to do the same thing to) has numeric values and what I want is to replace any numeric value above 0 with the same string value. So regardless of the number as long as its above 0 I want it to be that value. I've tried various combinations of IF formulas and converting the types back and fourth but I cant get it work.  

The most obvious formula to me was:

IF [Column Name] >= 0 THEN "Word" ELSE "" ENDIF

But that wont work because the column was double and I'm asking it to populate with a string. I tried doing it so it would populate with a number and I would then convert the number to a string but for some reason it just ignored the 0s and populated all the fields. 

So it was something like:

IF [Column Name] >= 0 THEN 1 ELSE 0 ENDIF

IF [Column Name] = "1" THEN "Word" ELSE "0" ENDIF

Any ideas?

Thank you

[grossal]

Hi @Phill,

I think a small sample dataset would be nice, but here are my thoughts:

I would go with the Multi-Field Formula for two reasons:

- You can apply one Formula to multiple fields

- You can change the data type of an existing column

Feel free to drop a sample and we'll work it out together or let me know if this was helpful.

Best 

Alex

[Phill]

Hi @grossal 

Thanks for the reply appreciate your help. 

I've attached a sample data set, using column headings A to E and random numbers in the rows. The 0's need to stay as 0's and the numbers need to be a value, for simplicity it can be the column header.

I tried out the multi-field formula tool and that can certainly replace values with the header name though the only way I can get it to only replace the numbers above 0 was to filter which meant having to filter off each columns then union it all back which caused some issues with the data as there are lots of other columns of data within this data set. 

Is there no way I can get Alteryx just to accept that I'm concerting a number into a word form a formula tool?

Thanks, 

Phill

[grossal · Accepted solution]

Hi @Phill,

I have attached an example workflow.

With the setting:

IIF is the short form of the IF THEN ELSE statement.

Hope this helps. Check out the workflow in the attachment.

Best

Alex

[Phill]

Thanks @grossal I used this and just made the "different Value" the column header and it worked perfectly all with 1 tool very efficient! 

Thank you!