Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Set -1 values as null

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?

Tags (1)
1 Solution

Accepted Solutions
Michael_Tarallo
Not applicable

Re: Set -1 values as null

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

Regards,
Mike Tarallo
Qlik
3 Replies
Michael_Tarallo
Not applicable

Re: Set -1 values as null

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

Regards,
Mike Tarallo
Qlik
Gysbert_Wassenaar
Not applicable

Re: Set -1 values as null

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.


talk is cheap, supply exceeds demand
sindhu_ibizcs
Not applicable

Re: Set -1 values as null

Hello gysbert,

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