How to count only the number of empty rows ?

[former_member188628]

Hi Gurus

         I am facing a problem related to displaying the count of specific object([Legacy Postal Code] and [Cleansed Postal code] in my case)which are having empty records.I have used IsNull() but it fails for those set of records which are having Numeric values and not displaying the exact count due to this.The IsNumber() function is not working as well.Could anyone help in providing the solution for this?Attached below the screenshots for reference:

case 1:

case2

case3

The legacy_count formula:

[former_member199945 · Accepted solution]

Hi

create varaiable as below for legacy postalcode & cleansed postal code .

v1= if(isnull(postalcode) then "1' else"0"

same to cleansed . now take count of v1 and use this in final output.

Thanks

[former_member188628 · Accepted solution]

Hi Seshu

       Thanks for the reply.But if we do as mentioned above then the problem seems to resolved for all the case except for the initial one:- count of BM records,which are displayed in case1.

Its showing the count as 4 instead should be 3.

Thank you

[former_member199945 · Accepted solution]

Hi Mality,

In this case you have to use and condition that is v3 = if( v1=1) and if(v2=1) then 1 else 0 . now take count of new variable  v3

v1 =legacy postal code null values

v2 =consolidated postal code null values

Thanks

[former_member188628 · Accepted solution]

Hi Seshu

      The variable v2:Consolidated Postal code null values is not clear to me.Could you please explain that further a bit or did u mean:Cleansed Postal code which is another dimension used in my reports.I have used "count([Legacy Postal code]) in case of Consolidated count,but its not working.

Thanks

Moumita

[former_member199945 · Accepted solution]

I mean create 2  count variables one for legacy postal & another for consolidated postal code as

v1= if(isnull(legacy postalcode) then "1" else"0"

v2=if(isnull(consolidatedpostalcode) then "1" else "0"

now take count v1& count v2 it will display null values count of both postal codes.

now create another variable with condition as  if(v1)="1 " and v2 ="1" then "1" else"0"

now take this final count in cleansed postal code output.

Thanks.

[former_member188628 · Accepted solution]

Hi Seshu

        I have tried your solutions but its producing the same previous output,I have made changes for the legacy Postal count alone,its still displaying the same..attached snapshot for referece.the newly created variables are :"Leg_v1" and Count_Leg_v1.The Count of records BM and BS are

incorrect.

[former_member199945 · Accepted solution]

Hi Maity,

What you are expecting I didn't understand you want to count null values or do you want  to ignore null values in count

if you wan to ignore count of null values then no need to create any variables .

based on sample data I applied just count onlegacy postal code & count of cleansed code as below.

Are u expecting this ? if so then just apply count function.

Thanks

[former_member188628 · Accepted solution]

Hi Seshu,

          What I needed was to ignore the Null values in the count.Counting the Null value is also good.

Thanks

[former_member188628 · Accepted solution]

Hi Seshu

         I am trying the same.. but  could not get the o/p as you got.Please....please.. help me on this.

The Count([Legacy Postal Code]) is showing the overall count ..including the null values(empty rows).I dont want Null values to be included in the count

Thanks

[former_member199945 · Accepted solution]

Hi maity,

Did  you created 3 different block s for cleansed country ? or you applied break on country?

I applied break on cleansed country and took count of legacy postal code & cleansed postal code .

as below .

Thanks

[former_member188628 · Accepted solution]

Hi Seshu,

     I applied breaks as well,but simply putting count(Legac Postal Code)) is not eliminating the null values as you got

Thanks

[former_member199945 · Accepted solution]

How come  its strange ,

Did you hide any dimension in the report other than those 4 objects ? if not  create new report using same query with  those fields and check either still you are facing same problem.

Thanks

[former_member188628 · Accepted solution]

Seshu,

        Could this be becoz of the custom sql present in the report??Coz my senior developer have created that?But I dont think so,coz the custom sql doesnot  seems that complex.??

Thank U