Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

set analysis, a field equal to selection field?

I have some tables which have more fields which should be the same ...

e.g.


table1:
LOAD customerid,
sku.something,
...
FROM ...
table2:
LOAD customerid,
other.something,
...
FROM ...


Is there a way without merging the tables to set sku.something equal to the already selected other.something???

I tried some things with set analysis but did not find the trick.

{< sku.something = $(other.something) >}

thx

Anita

1 Solution

Accepted Solutions
Not applicable
Author

I've fought with this yesterday,

I'm sure not It exists any easy solution :
in fact {< sku.something = {$(other.something)} >} will only work if you select exactly one value in other.something.

The GetFieldSelections (something like {< sku.something = {$(=GetFieldSelections(other.something,',',50)} >} will work if you select between 1 and 50 value of the other.selection (you can change the '50' value as you wish). multiple selections on other.something will works but not if there is no selection at all in other.something.

So the only way I found was to do something like

if( GetSelectedCount(other.something)>0,

....{< sku.something = {$(=GetFieldSelections(other.something,', ',50)} >} ....,

same expression without the sku.something = part. )

Not really efficient 🙂

Hope it helps

regards

View solution in original post

6 Replies
Not applicable
Author

I've fought with this yesterday,

I'm sure not It exists any easy solution :
in fact {< sku.something = {$(other.something)} >} will only work if you select exactly one value in other.something.

The GetFieldSelections (something like {< sku.something = {$(=GetFieldSelections(other.something,',',50)} >} will work if you select between 1 and 50 value of the other.selection (you can change the '50' value as you wish). multiple selections on other.something will works but not if there is no selection at all in other.something.

So the only way I found was to do something like

if( GetSelectedCount(other.something)>0,

....{< sku.something = {$(=GetFieldSelections(other.something,', ',50)} >} ....,

same expression without the sku.something = part. )

Not really efficient 🙂

Hope it helps

regards

Anonymous
Not applicable
Author

I just started with set analysis ... so maybe the question is not really intelligent ...

The part {< sku.something = {$(=GetFieldSelections(other.something,',',50)} >} ... if other.something has 2 selected fields this means those fields will be used for sku.something?

It might be a solution ... for the time being ... but I hope to find a nicer way without the constraints of 50 max (or whatever number we take) ... the fields should simply be the same.

Not applicable
Author

Bertrand's suggestion is a good one. GetFieldSelections() is a very useful function.

Some other tips. You really don't need to provide any other details to the function: GetFieldSelections(FIELDNAME) will get you a comma delimited list of the selections in that field.

If you have a complicated dollar sign expansion [$(...)] then it may be easier to use a variable. Instead of

Sum({<sku.something = {$(=GetFieldSelections(other.something))}>} Sales)


You can use:

Sum($(vVariableName) Sales)


Then define your variable as:

=if(GetSelectedCount(other.something)>0, {<sku.something = {$(=GetFieldSelections(other.something))}>})


Putting the logic in your variable helps keep your chart expressions clean and it can be reused multiple times.

Now, you may run into trouble when your field selections are strings. Strings require quotes around each selection, but the standard GetFieldSelections only puts commas in between. Here you can expand on Bertrands suggestion to use the other parameters of the function. This should work for strings:

=if(GetSelectedCount(other.something)>0,
{<sku.something = {"$(=GetFieldSelections(other.something, Chr(34) & ',' & Chr(34)))"}>})


Not applicable
Author

In all case that was a good question :).

I don't really know your needs but in my case, my aim is : I want to make selections on some fields on several sheets/objects but I don't want that selections to be applied to the full data model (because my selection may be useless or can create problems on some sheets). I think the true problem is I'm trying to use another logic than the QlikView native one (= all fieds are linked in the same functional perimeter). And I'm doing this because i'm tryning to reproduce an existing Dashboard (on a relational product) without adapting it to QlikView.

So, personally, I will :

1) Join sku.something and other.something and explain the QlikView logic to my users.
2) Or create several qvw (1 by functional perimeter) and navigate in them with actions (External / Open QlikView Document with Transfert State Option).
3) Or create 2 different unlinked datamodel in the same qvw file.

...

Anyway good luck 🙂

Anonymous
Not applicable
Author

We had the problem that the data with the 'something' selection was on was partly corrupt since the other 'something' was not selected at the same time which was needed for 2 of the 10 datapieces shown in our chart.

For now we have a workable solution ... thanks!

Anonymous
Not applicable
Author

Hello,

In my case I have two different star schemes (global and supplier scheme). Both of the schemes contain two tables (CALENDER and SUPPLIER).

What I wanted to do is a calculation in the global star scheme by applying the calender and supplier selections made in the supplier scheme.

So the formula I used was :

Sum({$<

[$(=Concat({1}distinct $Field,']=,[')&']='),                                         // do not take in consideration other selections                                                                                                   // made in the global star scheme

YEAR=p(YEAR_SUPPLIER), MONTH=p(MONTH_SUPPLIER),      // YEAR equals all possible values of                                                                                                  // YEAR_SUPPLIER

NAMESUPPLIER=p(NAMESUPPLIER_SUPPLIER)>}

AMOUNT)

I found the p() function interesting because it takes in account all possible values. So if I select NAMESUPPLIER_SUPPLIER, my Sum will take into account :

- the YEAR_SUPPLIER selected by the user

- or all possible YEAR_SUPPLIER (white fields) if no selection has been made by the user.