topic LOAD Blank and Null values in QlikView

LOAD Blank and Null values

Wed, 09 Jun 2010 22:44:00 GMT

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

LOAD Blank and Null values

tabletuner — Wed, 09 Jun 2010 22:55:00 GMT

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?

LOAD Blank and Null values

Oleg_Troyansky — Wed, 09 Jun 2010 23:00:51 GMT

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,

LOAD Blank and Null values

Thu, 10 Jun 2010 15:38:27 GMT

Thanks this worked Oleg.