Change Null to NA

[Haokun]

Hi

I have a set of data below, you will notice some of the columns are shown as 0 or null. So what I will need is for all the columns do not contain any "vs" in the header, I want to change the 0 or null to "N/A", for columns header contains "vs", which is to compare the data between 2 columns and if there is any data shown as "N/A" then the vs column will be shown as N/A as well.

Below is the current data

ENTITY ID	C-1M Fund	C-1M BM	MS-1M Fund	MS-1M BM	ES-1M Fund	ES-1M BM	C vs M-1M Fund	C vs M-1M BM	C vs ES-1M Fund	C vs ES-1M BM	M vs ES-1M Fund	M vs ES-1M BM
ASCIFD	-        1.11	-        0.62	2.23	2.23	-        1.11	-        0.62	-        3.34	-        2.85	0.00	-	3.34	2.85
ASGMAN	-        2.12	-        1.53	-        0.99	-        2.63	-	-	-        1.13	1.10	-        2.12	-        1.53	-        0.99	-        2.63
ASGMAN_D	-        2.12	-        1.53	-        1.98	-        2.63	-	-	-        0.13	1.10	-        2.12	-        1.53	-        1.98	-        2.63
ASPAIF	-        0.94	-        1.89	0.17	2.44	-        0.94	-        1.89	-        1.11	-        4.33	-        0.00	-	1.11	4.33
ASPAMF	-        3.62	-        3.45	-        3.03	-        2.66	-	-	-        0.59	-        0.78	-        3.62	-        3.45	-        3.03	-        2.66
ASPEEF	-        0.91	-        2.94	-        6.80	-        5.61	-        0.91	-        2.94	5.89	2.67	-        0.00	-	-        5.89	-        2.67
ASPEMF	-        1.65	-        1.21	3.77	2.09	-	-	-        5.42	-        3.30	-        1.65	-        1.21	3.77	2.09
ASPEMI	-        2.22	0.42	-        0.36	0.05	-	-	-        1.87	0.38	-        2.22	0.42	-        0.36	0.05

And the data output i want is 

ENTITY ID	C-1M Fund	C-1M BM	MS-1M Fund	MS-1M BM	ES-1M Fund	ES-1M BM	C vs M-1M Fund	C vs M-1M BM	C vs ES-1M Fund	C vs ES-1M BM	M vs ES-1M Fund	M vs ES-1M BM
ASCIFD	-        1.11	-        0.62	2.23	2.23	-        1.11	-        0.62	-        3.34	-        2.85	0.00	-	3.34	2.85
ASGMAN	-        2.12	-        1.53	-        0.99	-        2.63	N/A	N/A	-        1.13	1.10	N/A	N/A	N/A	N/A
ASGMAN_D	-        2.12	-        1.53	-        1.98	-        2.63	N/A	N/A	-        0.13	1.10	N/A	N/A	N/A	N/A
ASPAIF	-        0.94	-        1.89	0.17	2.44	-        0.94	-        1.89	-        1.11	-        4.33	-        0.00	-	1.11	4.33
ASPAMF	-        3.62	-        3.45	-        3.03	-        2.66	N/A	N/A	-        0.59	-        0.78	N/A	N/A	N/A	N/A
ASPEEF	-        0.91	-        2.94	-        6.80	-        5.61	-        0.91	-        2.94	5.89	2.67	-        0.00	-	-        5.89	-        2.67
ASPEMF	-        1.65	-        1.21	3.77	2.09	N/A	N/A	-        5.42	-        3.30	N/A	N/A	N/A	N/A
ASPEMI	-        2.22	0.42	-        0.36	0.05	N/A	N/A	-        1.87	0.38	N/A	N/A	N/A	N/A

[Emil_Kos]

Hi,

In a situation like this mutlifield formula is really useful. I have used below formula to sort your issue:

IIF([_CurrentField_]='-', 'N\A', [_CurrentField_])

When I copy pasted the null positions become '-'

In your exemple if nulls are really nulls you should use a formula like this:

IIF(isnull([_CurrentField_]), 'N\A', [_CurrentField_])

I have prepared a workflow for you: 

The output:

Just please remember if this will not work you need to replace the formula with this one: 

IIF(isnull([_CurrentField_]), 'N\A', [_CurrentField_])

If this was helpful please mark as a solution!

[Haokun]

hi @Emil_Kos 

thanks for your help, the multi field i can construct. But I need to do a comparison between 2 columns and which i need to change the data format to double. but when i change all the columns to double, i can not use this multi field tool to change the null cell to "N/A" coz it says the data type is different. In short, is that possible to have double and string data type in the same column?

[Emil_Kos]

Hi @Haokun,

If you really need N/A you need to have a string as a data type. Maybe you can compare the data in double and change the null's to 'N\A' afterwards?

[Haokun]

@Emil_Kos 

can not. If C-1 month is null, when i use Alteryx, C vs ES-1month column will show -1. But I need to show the result as "N/A" if there is no value under column C-1month

C-1month	ES-1month	CvsES-1month
1.5	1	0.5
(null)	1	N/A

[Emil_Kos]

Hi,

It looks it is is possible with double data type if you wil use table rules. Please see @AbhilashR post for reference:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/If-null-then-N-A/m-p/553241/highlight/...

[Qiu]

@Haokun 
Made some changes to the flow of @Emil_Kos .
Alteryx will not allow mixed Data Type in one column, so either String or Double.

Instead of replace with N/A, why not zero, which makes things easier.