count the null values in a Straight Table in qlikview

[#1]

Hi Every One

This Excel File I'm loading into qlikvew but in this ShopID having some Empty Cells is there i want to count the how many Null values are there in ShopID. and also Count the ShopNo Wise how many ShopId count and ShopId Null Count   ...

How to Write the Scripting to finding the NullCount  

see in below data

Regards

Sai Madhu

[agni_gold]

Are you want in load script ?

Or in set analysis.

[maxgro]

when you load from excel, add a field

load

    ShopNo,

    ShopID,

    Product,

    -len(trim(ShopID))=0 as IsNullOrEmpty

from

    yourexcel

then you can use

sum(IsNullOrEmpty)

a similar question with a solution in the chart

How to set null condition in set anslysis?

[Kushal_Chawda · Accepted solution]

Data:
LOAD *,
if(len(trim(ShopID))<=0,1,0) as Shop_Flag;
LOAD ShopNo,
ShopID,
Product
FROM
[Shop.xlsx]
(ooxml, embedded labels, table is Sheet1);

If you add more dimension , use aggr function, and aggregate over the shop No if you want result only by ShopNo

[agni_gold]

LOAD ShopNo,

     ShopID,

     if(IsNull(ShopID),'NULL','FULL')  AS ShopIDNullFlag,

     Product

FROM

(ooxml, embedded labels, table is Sheet2);

Just create one flag to identify or we can directly do in set analysis.

Please specify your requirements.

[#6]

Hi

It will be showing the error Like this

Error in expression:

')' expected

DATA:

LOAD ShopNo,

     ShopID,

     Product

     =len(Trim(ShopID)=0 as NullShopID

FROM

(ooxml, embedded labels, table is Sheet1)

[#7]

Thank you Very Much it will working

[Kushal_Chawda]

I am glad that it is working. please mark the correct answer and close the thread

[#9]

where i will put correct option give the guidance

[maxgro]

you miss the comma adter Product and a ) before =

load

    ShopNo,

    ShopID,

    Product,

    -len(trim(ShopID))=0 as IsNullOrEmpty

from

    yourexcel