Filter records from the Dimension

[#1]

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?

[desere_k]

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}

[tamilarasu]

Hi Nathan,

You can try expression as,

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

Or

If(Provider <>  'ONX', Provider)

[avinashelite]

you can try like this

//in dimension

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

[Kushal_Chawda]

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)

[Anonymous]

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');

[#7]

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