topic Re: Filter records from the Dimension in QlikView

Filter records from the Dimension

Wed, 25 Nov 2020 16:16:04 GMT

I imported data from .csv. I selected Charts and selected Table and dropped to the chart. I then dragged provider from Fields to the sheet. I was able to see the Provider. I added Tot Claim Amt, Tot Paid Amt measures as well. Now I wanted to filter certain providers. How do i do that. If it was SQL, I can say where Provider <> "ONX Inc" then select all the records. Here how do I it using the 'Expression'. I wanted to filter here at the Dimension level. Is my approach right?

Re: Filter records from the Dimension

desere_k — Mon, 14 Mar 2016 08:10:39 GMT

Hi Nathan, I'm not entirely sure what you want exactly but as far as I know the most efficient way would be to add that filter in the expression, rather than the dimension, as set analysis something like below

= SUM({<Provider <> 'ONX Inc'> Claim Amt}

Re: Filter records from the Dimension

tamilarasu — Mon, 14 Mar 2016 08:15:28 GMT

Hi Nathan,

You can try expression as,

Only({<Provider-={'ONX'}>}Provider)

If(Provider <> 'ONX', Provider)

Re: Filter records from the Dimension

avinashelite — Mon, 14 Mar 2016 08:18:00 GMT

you can try like this

//in dimension

if (dimension_name<>'ONX Inc' ,dimension_name) and check the suppress null

Re: Filter records from the Dimension

Kushal_Chawda — Mon, 14 Mar 2016 08:28:20 GMT

In front end object, create the calculated field as

aggr(if( not match(lower(trim(Provider)),'onx'),Provider),Provider)

or you can also use set analysis in expression.

sum({<Provider -= {'ONX'}>}Values)

Another better approach is to create the Flag in script

if( not match(lower(trim(Provider)),'onx'),1) as ProviderFlag

Now Your set analysis will become easy with below expression

sum({<ProviderFlag = {'1'}>}Values)

Re: Filter records from the Dimension

Anonymous — Mon, 14 Mar 2016 11:08:21 GMT

Hi Nathan,

use set analysis or if else statement.

sum({$<Provider ={'*'} - {'ONX Inc'}>} Amount)

OR at the script level you can import only those record where provider <>'ONX INC' .

WHERE(provider <> 'ONX INC');

Refer Below Code:

T2:

LOAD AA,

BB,

CV,

CD,

GH,

Items,

Used,

New

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE(CV <> 'List 1');

Re: Filter records from the Dimension

Mon, 14 Mar 2016 20:36:12 GMT

Thanks to everyone. Lot of examples. You guys are awesome.