topic Re: How to use FieldSelections to pull value from seperate table in App Development

How to use FieldSelections to pull value from seperate table

Anonymous — Mon, 22 Jan 2018 16:03:51 GMT

Hi there,

I am trying to build a visualisation in one of my Qlik Apps which essentially does the following, I will have a drop down list of advisers, for example:

Advisor
AA
BB
CC
DD
EE
FF

I will then have a seperate table with targets, but this can also include combined targets for 2 advisors, or more. Example being:

AdvisorSelection	Target
AA	0.5
AA,BB	0.7
AA,BB,CC	0.8
AA,BB,CC,DD	0.9
CC,DD	0.6
BB	0.7
CC	0.8
DD	0.9
EE	1

now it would be good noting there is no formula relating to AA's target and BB's - or they are not a calculated version but essentially depending on the selection on the app you use - it would bring up the selection for this target.

At the moment i can use GetFieldSelections(Advisor) so if i click AA & BB on the dropdown i get "AA, BB"

I would then like to look up this seperate table which is not joined to other data tables and bring through the target value. I have tried using FieldValue and FieldIndex but am not getting it to work, and Peek/ match functions are script only. This is for the chart side so any ideas would be greatly appreciated. It would also be good noting i'm only interested in if the values match something in the table so if i select something not in AdvisorSelection im happy for it to show blank aswell. Hopefully this makes sense?

Re: How to use FieldSelections to pull value from seperate table

ericasense — Tue, 23 Jan 2018 14:08:19 GMT

You could return the value through set analysis.

If you put the string into a variable, you can pass this into set analysis to return the one value that is associated with the string of names.

so set a variable vAdvisorNames=concat(distinct Advisor,',')

Then use this in the expression to pull the required value out of the table, if there are no matches it should return null

=Only({<AdvisorSelection={"$(vAdvisorNames)}>} Target)

Erica

Re: How to use FieldSelections to pull value from seperate table

Anonymous — Tue, 23 Jan 2018 15:02:41 GMT

Hi Erica

thanks for your reply, I think i am close but not quite there. I didnt think of using Variables.

I have got this in my load script as an example:

Names:

Load *

Inline [

name

Betacab

Astrida

Divadip

] (delimiter is '|');

targets:

Load *

Inline [

customer|target

Betacab|300

Astrida|500

Astrida, Betacab|600

] (delimiter is '|');

then on the variables i have vSelections which is getFieldSelections(name)

lastly i have tried the only formula, and cannot get this working with apostrophes and not:

=Only({<customer={$(vselections)>} target)

Any ideas what I might be missing?

Re: How to use FieldSelections to pull value from seperate table

Anonymous — Tue, 23 Jan 2018 15:09:01 GMT

Quick googling got it working! i tried finding similar posts, i was only slightly off:

Only({<customer={'$(=$(vselections))'}>} target)

this works now, perfect thank you!

Re: How to use FieldSelections to pull value from seperate table

ericasense — Tue, 23 Jan 2018 15:10:30 GMT

You're almost there, there was just a bit of syntax missing!

Provided that vSelections is returning what you need (does it always do it in the correct order for example?)

Then the syntax is:

=Only({<customer={'$(vselections)'}>} target)

You needed to wrap the variable expansion in quotes, and there was a curly bracket missing

Erica

Re: How to use FieldSelections to pull value from seperate table

Anonymous — Tue, 23 Jan 2018 15:13:41 GMT

Hi, this didnt work on my version of qliksense but the comment i put above does - odd! it looks like it would be right, but the extra dollar sign seemed to have fixed it.

As for the ordering, it will do the selections alphabetically as I have tested doing this backwards so this seems to work. Its not a great solution to a weird question as I ideally would like all the targets to add up or have some sort of logic behind, but it works for what I need!

Re: How to use FieldSelections to pull value from seperate table

ericasense — Tue, 23 Jan 2018 15:55:53 GMT

Just out of interest, in the variable expression editor is there a "=" before the expression?

and what does just writing "=vselections" return in a text box?

I think your variable might be returning the literal string of the function and not the resulting text that you want to insert.

So $(vselections) will return the string "getFieldSelections(name)" . the second set of variable expansion is then evaluating the function itself. It still works though!

Erica