Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
jmonroe918
Creator II
Creator II

Count Records - Filter out 'Yes' & Blanks

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

1 Solution

Accepted Solutions
sunny_talwar

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])

View solution in original post

9 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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])

Not applicable

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])