3 Replies Latest reply: Jan 12, 2017 2:40 AM by sindhu ja RSS

    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?

        • Re: Set -1 values as null
          Michael Tarallo

          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

          • Re: Set -1 values as null
            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 mtarallo's solution will work fine.