topic set analysis, a field equal to selection field? in QlikView

set analysis, a field equal to selection field?

Anonymous — Wed, 23 Dec 2009 19:58:33 GMT

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

set analysis, a field equal to selection field?

Wed, 23 Dec 2009 21:43:04 GMT

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

set analysis, a field equal to selection field?

Anonymous — Wed, 23 Dec 2009 22:05:52 GMT

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.

set analysis, a field equal to selection field?

Wed, 23 Dec 2009 22:06:40 GMT

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)))"}>})

set analysis, a field equal to selection field?

Wed, 23 Dec 2009 22:25:28 GMT

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 🙂

set analysis, a field equal to selection field?

Anonymous — Wed, 23 Dec 2009 22:29:48 GMT

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!

Re: set analysis, a field equal to selection field?

Anonymous — Wed, 22 Oct 2014 07:15:09 GMT

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.