Count Records - Filter out 'Yes' & Blanks

[jmonroe918]

I have a Table Chart where I am trying to perform the simple task of counting records where one of the fields either contains an empty value (blank or null, I assume) or contains 'No'. In other words I need the 'Yes' records filtered out of the total count.

The qvd is attached. I tried using:  =Count(  {<[Record DQ] -= {'Yes'}  >}   [Record.ID])

But it only returns the records with 'No'

Appreciate the help.

Jeff

[kaushiknsolanki]

Hi,

Try this in script.

Records:

LOAD

  ID,

     [Date Originated],

     [Issue Number] as [Record Number],

     If(Isnull([Complaint Disqualification]),'Null',[Complaint Disqualification]) as [Record DQ]

FROM

(qvd);

Now on front end use below expression.

=Count(

  {<[Record DQ] -= {'Yes','Null'}

  >}

  [Record.ID])

Regards,

Kaushik Solanki

[sunny_talwar · Accepted solution]

May be try this:

=Count(

  {<[Record.ID] = {"=Len(Trim([Record DQ])) = 0 or [Record DQ] = 'No'"}

  >}

  [Record.ID])

or this

=Count(

  {<[Record.ID] = {"=[Record DQ] <> 'Yes'"}

  >}

  [Record.ID])

[jmonroe918]

Kaushik & Sunny:

Thank you, both!  Your solutions worked out.

I think I'm going to go with Sunny's solution #2. Both of you gave me great ideas for use in other applications.

Thanks again!

Jeff

[jmonroe918]

Sunny:

An additional question if you don't mind...I'm using your second solution and want to add another filter.

The field name is "Failure" and I have a list consisting of "Shipping Damage", "Wong Product", "Incorrect Qty", etc. I want to filter out records with these selections (along with the "Record DQ" = Yes.).

So if a record has Yes and one of the selections listed, then the record is not counted.

Thanks in advance for your help.

Jeff

[sunny_talwar]

May be this:

=Count(

  {<[Record.ID] = {"=[Record DQ] <> 'Yes' and Not Match(Failure, 'Shipping Damage', 'Wong Product', 'Incorrect Qty')}

  >}

  [Record.ID])

or

=Count(

  {<[Record.ID] = {"=[Record DQ] <> 'Yes' or Not Match(Failure, 'Shipping Damage', 'Wong Product', 'Incorrect Qty')}

  >}

  [Record.ID])

From your description I was not sure if you need an OR or AND... but I guess try both to see which one works.

[jmonroe918]

Sunny:

I tried with with both And & Or, however no records displayed. I think your expression is missing the last "=". I placed it after the last selection (...'Incorrect Qty')=}. Qlik liked it, but the results were not correct. Like below.

Did I copy it incorrectly?

=Count(DISTINCT

  {<[Records.ID] = {"=[Record DQ]<> 'Yes' or

       Not Match([Failure],

       'Damaged During Shipping',

       'Wrong Product Sent',

       'Shipping Issue'},

       'Order Entry Issue',

       '*Lost During Shipment',

       'Duplicate Record')"}

  >}

  [Record.ID])

Jeff

[sunny_talwar]

I think I missed a double quote at the end

=Count(

  {<[Record.ID] = {"=[Record DQ] <> 'Yes' or Not Match(Failure, 'Shipping Damage', 'Wong Product', 'Incorrect Qty')"}

  >}

  [Record.ID])

[sunny_talwar]

I see an extra closing curly parenthesis. Remove that and also you have used *, is that for wildcard search? If that's what it is, then use WildMatch() instead of Match:

=Count(DISTINCT

  {<[Records.ID] = {"=[Record DQ]<> 'Yes' or

       Not WildMatch([Failure],

       'Damaged During Shipping',

       'Wrong Product Sent',

       'Shipping Issue',

       'Order Entry Issue',

       '*Lost During Shipment',

       'Duplicate Record')"}

  >}

  [Record.ID])

[#10]

Try This ...

Count(DISTINCT

  {<[Records.ID] = {"=[Record DQ]<> 'Yes' or

       Not Match([Failure],

       'Damaged During Shipping',

       'Wrong Product Sent',

       'Shipping Issue',

       'Order Entry Issue',

       '*Lost During Shipment',

       'Duplicate Record')"}

  >}

  [Record.ID])