Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Are you want in load script ?
Or in set analysis.
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
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
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.
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)
Thank you Very Much it will working
I am glad that it is working. please mark the correct answer and close the thread
where i will put correct option give the guidance
you miss the comma adter Product and a ) before =
load
ShopNo,
ShopID,
Product,
-len(trim(ShopID))=0 as IsNullOrEmpty
from
yourexcel