Solved: LOAD Blank and Null values - Qlik Community

Report Inappropriate Content · ‎2010-06-09

<body>I am trying to load blank and null values into the QV load statement. The 'disposition' value has blank and null values. I am not sure of the correct syntax or if this can even be done. I would like a 'list box' to be created that reduces the data based on the flag and dispositions criteria below. Basically the list box 'Orders' would contain one value called 'Display_Orders.' An end user would then just need to select 'Display_Orders' and the below flags/dispositions would be applied. <pre class="jive_text_macro jive_macro_code" jivemacro="code" ___default_attr="plain"> LOAD order_no ,complete, completed, quote_flag, disposition, if( quote_flag = 'N' AND disposition is 'NULL', //want this to be a NULL value; AND disposition = ' ', //want this to be blank projected_order; <and> </and> </pre> Let me know if you need anything else or have any other ideas about achieving this. Thanks for any help. </body>

Oleg_Troyansky · ‎2010-06-09

If you are trying to assign a NULL value, use function null(). If you are trying to test for null, there is a function isnull(), but it was proven to return inconsistent results between 32 bit and 64 bit environments.

What most developers use to test for both nulls, empty values and any number of spaces is the following condition:

len(trim(FieldName)) = 0

cheers,

Oleg Troyansky
Call me for expert support, performance tuning, advanced training and more!

View solution in original post

tabletuner · ‎2010-06-09

Your current if statement filter is too strong. I think you want the if statement to be as follows:


if(quote_flag = 'N' AND (disposition is 'NULL' OR disposition = '' OR disposition = 'B' OR disposition = 'D' OR disposition = 'S'), 'Display_Orders') AS Orders

Is this of any help?

Oleg_Troyansky · ‎2010-06-09

If you are trying to assign a NULL value, use function null(). If you are trying to test for null, there is a function isnull(), but it was proven to return inconsistent results between 32 bit and 64 bit environments.

What most developers use to test for both nulls, empty values and any number of spaces is the following condition:

len(trim(FieldName)) = 0

cheers,

Oleg Troyansky
Call me for expert support, performance tuning, advanced training and more!

Report Inappropriate Content · ‎2010-06-10

Thanks this worked Oleg.