if else formula

[Idyllic_Data_Geek]

I have a formula to replace the unknowns with blank...

if [Payee addr 1] = 'UNKNOWN' then [Payee addr 1] = ' '
else [Payee addr 1]
endif

The output I'm getting is replacing the unknown with 0. I have tried changing the data type from VWstring to just string... any idea what might be causing it?

[DawnDuong]

Hi @Idyllic_Data_Geek 

to avoid this unexpected outcome, you can use null() instead of “” in your current formula.

dawn 

[Luke_C]

Hi @Idyllic_Data_Geek 

Try the below. You do not need the [Payee addr 1] = '' in the then clause, that is actually another logical statement that alteryx is evaluating, which is why you're getting 0 as a result. 

if [Payee addr 1] = 'UNKNOWN'

then ''
else [Payee addr 1]
endif

[DawnDuong]

Sorry i read too fast, should be to replace the entire thing that is after then and before else.

otherwise you will get a true/false response because instead of assigning the value ‘’ Alteryx is assigning the result of the conditional evaluation (is x equal to y)

dawn

[Idyllic_Data_Geek]

@DawnDuong and @Luke_C Thank you. Both the solutions work. Another question 🙂 when I open the output excel file then it is giving me an error. Attached is the screenshot. Any idea what might be causing it and how can I resolve it?

[Luke_C]

@Idyllic_Data_Geek Can you share the configurations of your output tool?

[DawnDuong]

i had encountered this when i used filters and pivot tables in the excel files then the next time i use Alteryx to write data to the same excel, the pivot and filters in Other tabs were removed.

i have not found a fix for this though.