topic Re: count the null values in a Straight Table in qlikview in QlikView

count the null values in a Straight Table in qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: count the null values in a Straight Table in qlikview

agni_gold — Sat, 19 Mar 2016 06:57:55 GMT

Are you want in load script ?

Or in set analysis.

Re: count the null values in a Straight Table in qlikview

maxgro — Sat, 19 Mar 2016 07:01:17 GMT

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?

Re: count the null values in a Straight Table in qlikview

Kushal_Chawda — Sat, 19 Mar 2016 07:04:12 GMT

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

Re: count the null values in a Straight Table in qlikview

agni_gold — Sat, 19 Mar 2016 07:06:52 GMT

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.

Re: count the null values in a Straight Table in qlikview

Sat, 19 Mar 2016 07:08:57 GMT

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)

Re: count the null values in a Straight Table in qlikview

Sat, 19 Mar 2016 07:16:39 GMT

Thank you Very Much it will working

Re: count the null values in a Straight Table in qlikview

Kushal_Chawda — Sat, 19 Mar 2016 07:19:31 GMT

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

Re: count the null values in a Straight Table in qlikview

Sat, 19 Mar 2016 07:21:17 GMT

where i will put correct option give the guidance

Re: count the null values in a Straight Table in qlikview

maxgro — Sat, 19 Mar 2016 07:25:34 GMT

you miss the comma adter Product and a ) before =

load

ShopNo,

ShopID,

Product,

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

from

yourexcel

Re: count the null values in a Straight Table in qlikview

Kushal_Chawda — Sat, 19 Mar 2016 07:26:50 GMT

You will be able to see the "Correct answer" button on left bottom side of the reply

Re: count the null values in a Straight Table in qlikview

Sat, 19 Mar 2016 07:43:12 GMT

ya i got it Thank u verymuch