IF() with 'AND' argument not working in Data Manager

[peterkunhardt]

I'm attempting to create a calculated column in the data manager and receiving an error even though the script appears correct. 

I have the following expression:

if(isnull([price_per_segment]) AND [price] > 0,[price],[price_per_segment]*[num_segments])

This appears to work and is reported as "OK" when the script is evaluated in a chart or as a Master Measure or Dimension. (Although doesn't necessarily make sense for what I'm trying to do in that context; creating it as a master measure and wrapping it in a SUM() appears to work fairly well) 

However, when the same expression is used in the "Add calculated field" feature in the Data Manager, it refuses to generate a preview and reports "No preview. Enter a valid expression." A red underline appears on the final ")" in the expression. If I click "Create", it tells me "Missing right parenthesis" but I'm clearly not and adding more just seems to push out the error to the last ")". 

Removing either of the terms around the "AND" operator along with the "AND" itself results in a valid expression that I can execute.

Is this a possible bug or is there some limitation on IF() statements with AND operators in the load script that I'm unaware of?

[trdandamudi]

See if the below expression helps:

if(Len(Trim([price_per_segment]))=0 AND [price] > 0,[price],[price_per_segment]*[num_segments])

[peterkunhardt]

Same behavior, unfortunately, error indicator under the last parenthesis and "Missing right parenthesis" reported when I try to create the column. 

[trdandamudi]

I just created a sample code and ran on QlikSense and it is working:

Data:
Load *,
if(Len(Trim([price_per_segment]))=0 AND [price] > 0,[price],[price_per_segment]*[num_segments]) as new_field;
Load * Inline [
price_per_segment,price,num_segments
10,100,11
20,200,21
,300,31
];

Can you please try this and let me know. 

I am using QlikSense June server version.

[chriscammers]

Try putting parenthesis around your conditions

If((X=1 AND Y=2),Y,X)

[peterkunhardt]

(I'm on April '19 server if that matters)

@trdandamudi Your script works as expected when I put it directly in the load script of a new app. 

I can also apparently accomplish this with my existing app by creating a temp table with this script as part of the load statement and using RESIDENT to get my original table's content, dropping the original table and renaming the temp table to the original table name. (To accomplish this, I used the Data Manager to add a column without the second condition, unlocked the auto generated section of the script, copied it to 'Main', and edited in the second condition manually) My original syntax also works in this manner.

@chriscammers Adding the parentheses does not appear to resolve the issue in the Data Manager.  Instead the red "error here" cursor moves to the space after the 'AND' keyword and the DM reports "missing right parenthesis" the same as before. Your version of the script also appears to work in the load script and in the visualizations as the others do. 

So now we've got several versions of the script working in the Data load editor, and in visualizations, both with custom scripts and with scripts generated in the same manner as the Data Manager generates them, but all of them failing in the Data Manager's "add calculated column" feature. 

I'm willing to call that a bug at this point. Could be our installation, but I'm not sure where to go in the logs (or if there even are logs associated with a failed column create action) to find out.

Summary for readers finding this thread via search: The Data Manager's "add column" functionality may refuse to accept otherwise valid expressions in the April '19 release. To figure out whether it's your expression or a bug, try adding the columns in the data load editor manually with a statement like the following:

[temp_table]:
LOAD
[original_field_1],
[original_field_2],
[original_field_3],
if(Len(Trim([price_per_segment]))>0 AND [price] = 0,[price_per_segment]*[num_segments],[price]) as final_price
RESIDENT [original_table];
DROP TABLE [original_table];
RENAME TABLE [temp_table] to [original_table];

[t_witzgall]

Hi Peter,

did you open a Bug Ticket with Qlik Support? We are running April 2020 and experience the same issue.

Best regards

Tamara