Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Simple way to exclude all fields (100+) from set analysis except specified fields?

So I've been scouring the forums and have yet to see a simple answer to this question. If it exists, apologies!

Say I have a dataset with hundreds of fields.

I have loaded estimates via concatenation with only 4 of the corresponding fields....but the user may filter on any of the xxx-4 fields.

I want the estimates to always be displayed...and only change when a user filters on a field that corresponds to the estimates data.

Instead of writing a set like this:

=num(sum({$<[PL] = {'Finance'}, Fiscal_Quarter=, PROJECT_COST_CENTER =, DR_COST_CENTER=, PROJECT_NAME =, ETC...for every single field I want to ignore>} [Revenue Projection]),'##.#' )

....Is it possible to have my set only take the four fields that I specify? Rather than writing out the 90+ that I want to ignore?

1 Solution

Accepted Solutions
MVP
MVP

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Brain, the dollar sign expansion presented at the bottom of that post should basically expand to a complete list of the fields you want to clear (your 90+ field names).

Just add all field names you don't want to clear (i,e, selections should still be considered) to the element list here:

...Field-={'Stock','Manager'}...

12 Replies
MVP
MVP

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

May be something like this:

Sum({1<DontIgnoreField1 = $::DontIgnoreField1, DontIgnoreField2 = $::DontIgnoreField2, DontIgnoreField3 = $::DontIgnoreField3, DontIgnoreField4 = $::DontIgnoreField4>}[Revenue Projection])

or this

Sum({1<DontIgnoreField1 = p(DontIgnoreField1), DontIgnoreField2 = p(DontIgnoreField2), DontIgnoreField3 = p(DontIgnoreField3), DontIgnoreField4 = p(DontIgnoreField4)>}[Revenue Projection])

Not applicable

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Hi Stefan, thank you for your response!

I may have misinterpreted that post when I was looking at it earlier...but I thought it was referring to excluding certain selections within a field, not the fields themselves. Please help me understand your reasoning...or where I misinterpreted the post/can apply the same logic to excluding fields. Appreciate it

atkinsow
Valued Contributor II

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Probably not a 'simple' way.  You could see if this meets your need:  https://community.qlikview.com/thread/60630.

Otherwise, in your script you could create a variable listing all the field names you do NOT want to be filterable. 

Something like:

Let  vExcludeFIelds='Fiscal_Quarter=, PROJECT_COST_CENTER =';


But you could have the script loop through all the fields so that it builds the variable for you with all the fields you want to not be filterable.

Then in your expression you would only need to reference the variable. Something like:

=num(sum({$<[PL] = {'Finance'}, $(vExcludeFields)>} [Revenue Projection]),'##.#' )

Not applicable

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Hi Sunny,

Thank you for the expressions.

Tried both of these but they did not appear to work.

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

When you mention they don't work, can you elaborate what is the issue you are running into? Would you be able to share a sample for us to help you better?

MVP
MVP

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Brain, the dollar sign expansion presented at the bottom of that post should basically expand to a complete list of the fields you want to clear (your 90+ field names).

Just add all field names you don't want to clear (i,e, selections should still be considered) to the element list here:

...Field-={'Stock','Manager'}...

Not applicable

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Hey Wallo,

Great thinking! Thanks for the help!

MVP
MVP

Re: Simple way to exclude all fields (100+) from set analysis except specified fields?

Sunny, p() function will still be influenced by selections in other field.

$::FIELD seems not to work (at least in my version), if you expect to copy the default state selection of field FIELD.

Might work if you use an additional alternate state and use that in the aggregation set identifier, though.

Community Browser