Set -1 values as null

[#1]

The original application from which our data is generated uses -1 to represent a null value. What is the proper syntax for replacing -1 values (in any column) with Null in the Qlik Sense Data Load Editor?

[Michael_Tarallo · Accepted solution]

Hi Zachary - I apologize for the delay - I see no one has responded to your post just yet. Just to be clear - are you looking at "transforming" the data (so to speak) so when the value comes in as -1 - you actually want to replace it with a "null" value - into the "loaded" - in memory data model?

Example data (attached)

dim1    meas1
value1    10
value2    20
-1    30
value3    40
-1    50
value4    60

If so you can you try this syntax in the script editor before the load statement:

Qlik is precise as how it handles Nulls - so a simple replace() function might not work for you. Here is what I have found.

Set NullInterpret = '-1';

Set NullInterpret = '-1';

LOAD
    dim1,
    meas1

FROM [lib://Test/replace_nullvalues.csv]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

You may also want to take a look at this document - to learn more about Null handling. It refers to QlikView but is applicable to Qlik Sense too.

Please mark the appropriate replies as helpful / correct so our team and other members know that your question(s) has been answered to your satisfaction.

Let us know how you do.

I attached the sample data and the .qvf file to this document - you can copy the .qvf file to C:\Users\<user profile>\Documents\Qlik\Sense\Apps  - and refresh the Desktop hub using F5.

Regards,

Mike Tarallo

Qlik

[Gysbert_Wassenaar]

I don't recommend creating Null values as nulls cannot be selected and are hard to work with in expressions. But if you insist... add these lines above the rest of your script:

mapNull:

LOAD -1 as Key, null() as Value

Autogenerate(1);

MAP * USING mapNull;

Edit: Setting the NULLINTERPRET variable works for text and excel files, but not for database sources. So if you're only loading from text or excel files then Michael Tarallo's solution will work fine.

[Anonymous]

Hello gysbert,

how can we insert null values in files..thanks in advance.pls help me to get a solution.