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

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

Wed, 18 Jan 2017 21:42:18 GMT

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?

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

swuehl — Wed, 18 Jan 2017 21:54:35 GMT

Maybe like this?

Ignore all selections except some specific fields using Set Analysis

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

sunny_talwar — Wed, 18 Jan 2017 21:56:01 GMT

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])

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

Wed, 18 Jan 2017 22:13:36 GMT

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

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

Anonymous — Wed, 18 Jan 2017 22:19:58 GMT

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]),'##.#' )

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

Wed, 18 Jan 2017 22:20:01 GMT

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?

sunny_talwar — Wed, 18 Jan 2017 22:21:19 GMT

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?

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

swuehl — Wed, 18 Jan 2017 22:24:07 GMT

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'}...

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

Wed, 18 Jan 2017 22:25:17 GMT

Hey Wallo,

Great thinking! Thanks for the help!

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

swuehl — Wed, 18 Jan 2017 22:27:30 GMT

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.

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

Wed, 18 Jan 2017 22:32:41 GMT

Thank you for the explanation, this works great!

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

Anonymous — Wed, 18 Jan 2017 22:33:07 GMT

Thanks for the link! Definitely keep it in mind if I ever need to do that.

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

sunny_talwar — Wed, 18 Jan 2017 23:25:07 GMT

I guess I do understand you response related to p(), but $:: stopped working??? Which version are you on?