Malformed IF statement with concatenant

[keithlawrence]

Hi,

I'm pretty brand new to Alteryx (2 days) so I'm going to ask a simple question. As a business, we're moving the script from QlikView to Alteryx so it's like trying to learn a new language and I'm failing :(

Why is this formula giving me a Malformed IF statement:

If IsNull([PO Line Catalogue ID])

THEN

Null()

ELSE

[PO Line Catalogue ID] + " - " +

Uppercase([PO Line Description]) + " - " +

[PO Line Unit Price - Original] + " - " +

[PO Line UOM] + " - " +

[PO Line eClass Code]

I'm sure it's easy but I can't figure out what's wrong

[vishwa_0308]

Hi @keithlawrence,

Failing is the part of learning great things, One can't learn if he/she doesn't fail ;) You are good but just missed "endif" at the end of the expression, just add it and you are done :)

Best,

Vishwa

[keithlawrence]

Thanks Vishwa.

I did try that as I read somewhere about the END IF but I was still getting the same error.

I changed the formula to

IIF(IsNull([PO Line Catalogue ID]),

Null(),

[PO Line Catalogue ID] AND " - " AND

Uppercase([PO Line Description]) AND " - " AND

[PO Line Unit Price - Original] AND " - " AND

[PO Line UOM] AND " - " AND

[PO Line eClass Code]

This at least got me past the Malformed IF statement but everything that is not null is returning 0 rather than a concatenation of the fields. I basically trying to create a key field so I can link table together when I push back out to Qlik. Any ideas?

Keith

[vishwa_0308]

you want to concatenate the fields in case of non-null data then please use "+" operator rather than "add" because its a logical operator that is the reason its giving you zero. please use your earlier expression and that will give you the expected results.

best,

vishwa

[keithlawrence]

Hi Vishwa,

I'm using AND not ADD.

I've reverted to my original statement and added END IF at the end and still get a malformed IF statement

[vishwa_0308]

sorry for the typo its "AND" only..please try this :

If IsNull([PO Line Catalogue ID])

THEN

Null()

ELSE

[PO Line Catalogue ID] + " - " +

Uppercase([PO Line Description]) + " - " +

[PO Line Unit Price - Original] + " - " +

[PO Line UOM] + " - " +

[PO Line eClass Code] endif

[keithlawrence]

Sorry but that's what I have and it's still not working. See attached screenshot. There's an error at char(219)

[vishwa_0308]

Could you please share some sample data..hope all those fields are string type.

[keithlawrence]

Unfortunately, I can't share any data as it's highly sensitive.

All the fields are not strings

PO Line Catalogue ID = V_String / Size = 255

PO Line Description = V_String / Size = 255

PO Line Unit Price - Original = Double

PO Line UOM = V_String / Size = 255

PO Line eClass Code = V_String / Size = 8

[jdunkerley79 · Accepted solution]

You have an extra space in the END IF at then end 

IF ISNull() THEN
   Null()
ELSE
   [PO Line Catalogue ID] + " - " +
   Uppercase([PO Line Description]) + " - " +
   ToString([PO Line Unit Price - Original]) + " - " +
   [PO Line UOM] + " - " +
   [PO Line eClass Code]
ENDIF

 Added a ToString for the numeric field