Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Malformed IF statement with concatenant

keithlawrence
6 - Meteoroid

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

12 REPLIES 12
vishwa_0308
11 - Bolide

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
6 - Meteoroid

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
11 - Bolide

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
6 - Meteoroid

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
11 - Bolide

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
6 - Meteoroid

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
11 - Bolide

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

keithlawrence
6 - Meteoroid

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
ACE Emeritus
ACE Emeritus

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

Labels