community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Malformed IF statement with concatenant

Highlighted
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

Alteryx Certified Partner

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

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

Alteryx Certified Partner

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

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

Alteryx Certified Partner

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

Meteoroid

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

Alteryx Certified Partner

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

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

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