0 Replies Latest reply: Jun 29, 2016 7:38 AM by Gregorio Ginestri RSS

    Why MS SQL and Qlick Sense show different counting results for empty cells?

    Gregorio Ginestri

      Hi everybody,

      i'm struggling to use the functions count/Nullcount/missingCount/isNull and similar to obtain the right number of emply cells of an array.

       

      In my Qlick sense sheet, i loaded correctly the data from an OLE DB but i didn't insert any set condition for the Null interpreting in the script.

      Using Microsoft SQL management studio 2014, i made a query in SQL to count the empty cells of a field named "Campo" and the query shows me 32955 empty cells. This is the right number of empty cells present in the array.

      Following, i attach the query inserted in SQL to obtain the previous result:

      Select Count(Valore)

      From dbo.Campi

      Where Valore=''            //('' is made with a couple of ')//

       

      I'm sure that the DB has been loaded with empty cells (so this cells exist but without any value inside).

       

      Insted, if i use Qlick sense to make a KPI that simply counts the emply cells from the field Valore, Qlick sense returns me differents results:

      if i use the expression count (Valore)='', the KPI returns me 0;

      if i use the expression NullCount(valore), the KPI returns me 0;

      if i use the expression MissingCount(Valore), the KPI returns me 70878. In particular this last result makes me confused, beacuse i can't explane it to me.

      if i use the expression isNull(Valore), the KPI returns me -1.

       

      In my opinion i have to insert in the script a particular condition to let qlick sense to consider the emplty cells as a particular value with the function NullInterpret. i tried this but without any success.

       

      Anyone can help me?