how to count null value in a field

[rohit214]

hi all

i have feild name invadate

i want to count null value only

please help me

thanks &regards

rohit

[Sokkorn]

Hi rohit214,

In load script let try this

IF(LEN(TRIM([invadate]))<0,1,0)     AS NullFlage

HTH and let me know.

Regards,

Sokkorn Cheav

[rohit214]

hi Sokkorn

thank for your reply it shoes me 15 but the no of null values is 12

it is not workinf fine

rhanks

rohit

[jagan]

Hi Rohit,

Hope this helps you

=Sum(if(isnull(invdate) OR Len(Trim(invdate)) = 0, 1, 0))

Or try the function

NullCount(invdate)

Regards,

Jagan.

[SunilChauhan]

count( if(isnull(invdate),invdate))

or

count( if(len(invdate)=0,invdate))

[rohit214]

hi jagan

i want result in text object

null value=12

but your solution is giving me 15

which is not correct

thanks

rohit

[rohit214]

hi sunil

your solution gives me 0

thanks

rohit

[SunilChauhan]

can you attached the sample.qvw file  after reloading your excel

[jagan]

Hi Rohit,

Will you upload the QVW file which you are working.  When I loaded your excel file in QVW i am getting 0 by using the expression

=Sum(if(isnull(invdate) OR Len(Trim(invdate)) = 0, 1, 0))

When I referred the table values i didn't found any missing or null values in it.  I think the empty are not loaded during the loading.

Regards,

Jagan.

[jagan]

Hi,

When I manually given single space in the empty cells in excel file, i got the count which you expected i.e,.12

by using the following expression

=Sum(if(isnull(invdate) OR Len(Trim(invdate)) = 0, 1, 0))

OR

=MissingCount(invdate)

Regards,
Jagan.