topic Count Records - Filter out 'Yes' & Blanks in QlikView

Count Records - Filter out 'Yes' & Blanks

jmonroe918 — Thu, 23 Feb 2017 20:24:43 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

kaushiknsolanki — Thu, 23 Feb 2017 20:29:37 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

sunny_talwar — Thu, 23 Feb 2017 20:30:49 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

jmonroe918 — Thu, 23 Feb 2017 21:09:43 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

jmonroe918 — Thu, 23 Feb 2017 23:28:00 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

sunny_talwar — Fri, 24 Feb 2017 00:02:24 GMT

May be this:

=Count(

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

[Record.ID])

=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.

Re: Count Records - Filter out 'Yes' & Blanks

jmonroe918 — Fri, 24 Feb 2017 00:40:48 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

sunny_talwar — Fri, 24 Feb 2017 02:22:54 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

sunny_talwar — Fri, 24 Feb 2017 02:24:26 GMT

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

Re: Count Records - Filter out 'Yes' & Blanks

Fri, 24 Feb 2017 02:26:12 GMT

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