Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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.
Hello gysbert,
how can we insert null values in files..thanks in advance.pls help me to get a solution.