topic Re: Record Count not Changing when Filtering in QlikView

Record Count not Changing when Filtering

laynieyip — Sat, 16 Nov 2024 21:35:07 GMT

I have a total number of events.

I want to filter one of my dimensions to not include "TEST SITE" using where SITE_NAME <> 'TEST SITE' - so my count should be 170,232 to filter out the 3 I do not want included.

My script looks like this:

Site:
LOAD SITE_ABREV_NAME,
SITE_ID,
SITE_NAME
FROM
[S:\Files\Prod\Site.qvd]
(qvd)

where SITE_NAME <> 'TEST SITE'
;

The TEST SITE disappears from my graph, but the count does not change. Instead the "TEST SITE" changes to "-"

I've tried using Where Not Match (SITE_NAME, 'TEST SITE') and the same thing happens.

How do I get this to filter out and get the correct count? Please help!

Here is where the TEST SITE shows in the Graph.

When I apply the filter, it "works", but the count does not change because the name of the TEST SITE changed to " - ".

Re: Record Count not Changing when Filtering

vvira1316 — Thu, 17 Jan 2019 16:12:54 GMT

is SITE_ID, SITE_NAME coming only from this QVD in your data model? It may be present in some other information and because of that you may be seeing null value of the dimension.

If that is not the case then can you please provide sample app with mock data to help you further?

Re: Record Count not Changing when Filtering

laynieyip — Thu, 17 Jan 2019 16:20:07 GMT

It is only coming from this QVD and I'm just trying to filter out this dimension. I've even tried using:

where SITE_ID <> '10014'

but that also does not yield the results I am looking for. When I download to excel, I see that the column name is REPLACED with " - " where it used to be "TEST SITE" - and therefore still being counted.

Re: Record Count not Changing when Filtering

vvira1316 — Thu, 17 Jan 2019 17:39:49 GMT

I created a quick sample and it works fine. Without actually seeing your data/file it will be difficult to say what may be causing it. You may want to trim values to make sure spaces are not causing the problem.

I'm attaching the sample data and QVW file that works fine to remove the required records and count.

Re: Record Count not Changing when Filtering

laynieyip — Thu, 17 Jan 2019 18:50:19 GMT

Thank you for the sample .Zip. This confirms I'm not doing anything wrong. It SHOULD work, but it's not.

Oddly, I found a workaround where if I put use an Inner Join to my fact table then it works.

Inner Join (Events)
LOAD SITE_ABREV_NAME,
SITE_ID,
SITE_NAME
FROM
[S:\Files\Prod\Site.qvd]
(qvd)
where SITE_ID <> 10014
;