topic Re: Remove selection in Multiple Fields with Set Analysis in QlikView

Remove selection in Multiple Fields with Set Analysis

Anonymous — Tue, 04 Mar 2014 14:23:35 GMT

Dear Community,

I've created a star schema datamodel with many dimension and multiple facts in the central fact table.

For certain analysis I'd like to remove selection in multiple fields with Set Analysis e.g. Sum({$<ProductName=>} OrderCount).

As there are a lot of Dimensionen (>30) and der remove Selection Set should later be dynamic I wondered if there is a way to remove selection of Fields in a more efficient way. I was thinking of either using simply the keys to the dimensions like %KEY_Product in order to remove all the Fields in the Product Dimension. Or using a inverse logic to keep only those selections of certain fields an ignore all other.

For both ways I've no Idea how to realise it! Any help is appreciated!

Thank you!

BR
Andreas

Re: Remove selection in Multiple Fields with Set Analysis

Tue, 04 Mar 2014 14:31:53 GMT

Hi Andreas, you can create the set analysis expression dynamically.

Firstly, create a table with one field having required dimensions.

_DIM:

LOAD * INLINE [

_DIM_SET

];

create the variable LET vSETExp = '{$<' & concat({1}_DIM_SET , '=,') & '=>$'

the above value will return like below:

{$<A=,B=,C=,D=>}

Re: Remove selection in Multiple Fields with Set Analysis

Anonymous — Tue, 04 Mar 2014 14:43:51 GMT

Thank you! Your post shows how final result will/should look like!

Thus, my question was more about if there is a way to remove selections of fields more efficiently e.g. remove a entire set of Field (Table) via the KEY Field or inverting the logic!

Thank you!

Re: Remove selection in Multiple Fields with Set Analysis

Tue, 04 Mar 2014 14:50:21 GMT

As of my knowledge we can't apply the wildcard search on the Field names in the Set Analysis like below:

Sum( {$<%KEY*=>} Amount) --> This is Incorrect.

the other way is create the dynamic set expression with field starting with %KEY_* like below:

vDynamicExp = Concat( {1<[$Field]={'%KEY_*'}>} '{$<'& [$Field] &' )', '=,') & '=>}'

and call this variable $(=vDynamicExp)

Re: Remove selection in Multiple Fields with Set Analysis

Tue, 04 Mar 2014 14:51:07 GMT

Hi Andreas,

I think a solution to this problem will be to hide the fields you don't want users to see.

By doing this you need to do the following:

Add set hideprefix = '_'; and then add underscores to all the fields you want to hide. Once you do a full reload all the fields starting with an underscore will be hiden from users.

Regards,
Marius

Re: Remove selection in Multiple Fields with Set Analysis

Anonymous — Tue, 04 Mar 2014 15:20:01 GMT

I do not want to hide the fields as they are necessary for other facts. It's only necessary the deactivate them for certain expression. It's just that they are so many. But I think i will go with dathu.qvs way first and build somehow a string containings those fields.

Thank you