Why MS SQL and Qlick Sense show different counting results for empty cells?
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:
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.