topic Re: Set Analysis Mutiple Fields selection in QlikView

Set Analysis Mutiple Fields selection

manideep78 — Mon, 12 Apr 2021 21:07:49 GMT

Dear ALL,

Can you please let me know if all 3 expressions returns same result?

1. Count({< Col1={'A'}, Col2={'A'}, Col3={'A'}, Col4={'A'}>} id)

==>

2. Count({<Col1,Col2,Col3,Col4={'A'}>} id)

==>

Set vVar = 'Col1,Col2,Col3,Col4='

3. Count({<$(vVar){'A'}>} id)

The reason I want to use the third expression is I have too many columns.

Re: Set Analysis Mutiple Fields selection

marcus_sommer — Tue, 13 Apr 2021 08:05:24 GMT

Expression 1 and 2 aren't identically and won't return the same results.

If you says that you have too many columns and wants therefore to shortcut your expressions I have the impression that you tries to solve challenges within the UI which are better addressed within the script, maybe by transforming (a part of) your data per crosstable into a "normal" data-structure.

- Marcus

Re: Set Analysis Mutiple Fields selection

manideep78 — Mon, 26 Apr 2021 18:51:08 GMT

Hi @marcus_sommer

Thanks for the response and sorry I could not reply you back in time.

My requirement is return 'Ids' if any of the listed field has value 'A'. Here I gave the fields in set anaylsis expression.

Instead, I want to choose the field(s) to look for value 'A' and return Ids.

I have created an Inline table with same field names. So, is it possible to put the columns in variable and look for 'A' once in All fields?

Example:

InlineTable:

ColumName

Col1,

Col2,

Col3,

Col4

vVar = 'Col1, Col2, Col3, Col4'

Count({< vVar = {'A'}>} id)

P.s: I might be wrong but this is my requirement. Look for 'A' in any of the chosen fields and return Id. OR conditions should be used

if user selects Col1 and Col2 from ColumnName listbox, return ids if 'A' exists either in Col1 or Col2.

If 3 columns are selected, Col1 or Col2 or Col3.

please suggest.

Thanks

Re: Set Analysis Mutiple Fields selection

marcus_sommer — Tue, 27 Apr 2021 07:25:41 GMT

Like above hinted I believe that your datamodel isn't really suitable for such a view and a transforming of the structure may simplify the matter - especially if there isn't only one multi-field matching/calculating else several ones.

Nevertheless you may solve it with a set analysis but you need to define it properly - it's regardless if it's done with external variables or maybe a $-sign expansion or just manually written - means you need to define all included fields separately with the wanted values and also applying the intended linking of them respectively the needed operators.

In your case with an extra field to define the fields which should be included such an expression may look like:

= concat('< ' & COL & ' = {''A''}', ' > + ') & ' >'

- Marcus