topic Re: Set-analysis '+' operand not creating unioned value list, when combining Alternate States select in QlikView

Set-analysis '+' operand not creating unioned value list, when combining imperfect key selections

evan_kurowski — Sat, 16 Nov 2024 19:02:42 GMT

Hello Qlik Community,

Had been getting tormented by this relatively straightforward set-operand expression involving the '+' union of selections drawn from two alternate states.

The app has two alternate states [StateA] & [StateB], and the expression attempts to union selections from field F1 across both states, into a single list box.

=Aggr( Only({[StateA] + [StateB]} F1), F1)

This technique has worked pretty consistently in the past, and it wasn't immediately clear what was causing the issue, but I may have been able to narrow down some repeatable conditions observed during breakdown.

What I am encountering is the '+' set-operand will union the proper value list from the key-field only when all values fell within the first table loaded into the combined key field. The expression still produces correct results in the list box when values found in only one of the multiple contributing sources are selected. (selecting 'alpha','beta' in [F1])

However, when the key-field is built from two sources where expression tries to select values exclusive to the first table built into they key, the '+' set-operand starts functioning erratically.

So the syntax in the example for field [F1] is working, but it can be broken by loading a leaf table into the key with even a single exclusive value to the extant field values.. e.g.:

Re: Set-analysis '+' operand not creating unioned value list, when combining Alternate States select

evan_kurowski — Wed, 26 Feb 2020 16:23:18 GMT

(continued...)

*note: i keep having to post threads in multi-parts because the website in mid-edit just starts REFUSING further edits. I'm not sure if this is because my host doesn't want content getting out, or whether the Qlik portal doesn't want content getting in, but... from an end-user perspective, the posting editing experience is frequently broken.

Like it literally becomes a struggle to compose a few brief paragraphs in Notepad and merely copy them over to the Qlik editing pane. If we're getting hacked...we should sue... for ALL OF THE DAMAGES. Kidding... partially. Maybe.)

In the inverse, the syntax for [F2] only seems to be "fixed" by loading a new table that contains ALL possible values of the [F2] keyfield to that point. Merely loading a new table with just any two mutual values between the existing contributing sources is not enough to fix the expression (even though only partial overlap was sufficient for the first example).

//**even appending a single exclusive value to the key-field breaks the '+' set-operand
//[THIS_BREAKS_F1_SYNTAX]:
//NOCONCATENATE LOAD F1, F1 AS F1.SOURCE3 INLINE [
//F1
//oopsibreakit
//];

Please take a look and let me know your thoughts. Does this constitute a bug? It seems like a very tricky set of conditions where if you carelessly add even one value to a multi-source key field, suddenly all the set-analysis is acting up. Thanks for your consideration all!

Re: Set-analysis '+' operand not creating unioned value list, when combining Alternate States select

evan_kurowski — Thu, 27 Feb 2020 03:10:09 GMT

**update** I've revised the attached sample .qvw which includes field example F3.

Further examination adds a bit more clarification. The set-operand from field F1 appears to work, because the second load statement contributing to F1 is a complete subset of the first load statement. (but I don't think the set-operand is actually working in that example)

When i try these examples on a new field [F3], which comprises a key-field of 3 unioned values, 2 contributed from source 1, and 2 contributed from source 2, with only a single common value between them.

A single set-modifier condition, in the default state will successfully parse a value list from a multi-table key field.

//the single condition set-modifier is able to combine values from the first & second load statements contributing to Field F3

=Aggr( Only( {<F3-={"kappa"}>} F3), F3)

One the set-analysis involves a set-operand, the expression then seems only able to evaluate values from the first load statement that contributed to the key field.

//the set-operand cannot "see" the values from the second load statement contributing to Field F3

=Aggr( Only( {[StateA] + [StateB] } F3), F3)

Also added example for field F4, to demonstrate the set-operand alternate state technique works fine & shows no issues when used on a non-key field.

Re: Set-analysis '+' operand not creating unioned value list, when combining Alternate States select

marcus_sommer — Thu, 27 Feb 2020 14:28:08 GMT

I think your observed behaviour is related to the technically implementation of the alternate states. Without knowing the specifics it's difficult to say if it's an expected behaviour by applying the states on keys - intentionally designed in this way or as a side-effect and the documentation is just missing to mention this kind of restriction - or if it's an unexpected behaviour respectively a bug.

AFAIK the state of a selection is some kind of parallel-table to the system-table. If there are multiple states enabled Qlik needs to use multiple ones or to extend the state-table to further fields. I would now expect that it shouldn't matter if the field is a key or not because of the relation to the system-table. It's really just guessing. Because of the fact that's not working I assume that there is something different and it has also a relation to the data-tables ...

I believe you found already two ways to bypass it - by using an adjusted datamodel in which those fields are not keys or to ensure the use of a perfect key by adding the missing key-values from the other side. It may sound like a lot of efforts just for using some special alternate state scenarios but I wouldn't too easily discard this consideration - it might have further advantages, too.

Another approach could be also to adjust the expresion-logic for the listboxes, maybe something in this way:

= aggr(if(count({[StateA]} F2) or count({[StateB]} F2), only(F2)), F2)

- Marcus

Re: Set-analysis '+' operand not creating unioned value list, when combining Alternate States select

evan_kurowski — Thu, 27 Feb 2020 16:49:21 GMT

Thank you for responding Marcus, your input is always valuable & insightful.

Looks like we agree on observations, it definitely appears data table related.

Two more examples reaffirm scripting order matters. The same set of field values & expression yield two different results based on the order in which load statements contributed values to the key-field. Set-operands used in conjunction with alternate states seem to only perceive the first table loaded into a key.

Your approach to recreate the result set in other forms is just great! Usage of the Count() function as boolean is quite inventive. Since this IF() expression can assemble the "correct" result set, it seems set-operand functionality should be able to learn from this, emulate techniques applied and be capable of recreating the same.

That way set-operand usage would have no special considerations when applied to key-fields vs. non-keys, or usage in a default vs. alternate state context.

Otherwise set-operand "caveats" would get cumbersome. (i.e. "You can use set-operands, but when using in conjuction with alternate states, then only with perfect keys or non keys, or with imperfect keys but only with the first table loaded" ... so many conditions to remember)

Thank you again Marcus.

Re: Set-analysis '+' operand not creating unioned value list, when combining Alternate States select

evan_kurowski — Fri, 13 Mar 2020 14:44:54 GMT

Well Marcus, was giving this thread a little time to see if Qlik might pop-in and say "Roger that, we're on it."

But it looks like the issue is pretty clearly identified. Thanks so much for your assistance, it is very appreciated. You definitely deserve that MVP & Luminary status.