Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
williamandersson
Partner - Contributor III
Partner - Contributor III

How do I make Field Selections in the script (equivalent to set-analysis in frontend)

Hi!

How can I better make selections of values in a field which I then store into a new field, without using if-statements. 

Below I only need a few values of the field. Is there a smarter way of doing this? I'm not able to do this with a set-analysis frontend due to the use case being a Trellis chart, where it seems like I am unable to make limitations to the dimension.

Binary [111be469-0215-4cc5-9bd6-ba9ddff59f95];

tmpTable:
load
[Regional Sales Mgr],
[Regional Sales Mgr Name],
if([Regional Sales Mgr] = 'S1', [Regional Sales Mgr Name],
if([Regional Sales Mgr] = 'S2', [Regional Sales Mgr Name],
if([Regional Sales Mgr] = 'S3', [Regional Sales Mgr Name],
if([Regional Sales Mgr] = 'S4', [Regional Sales Mgr Name],
if([Regional Sales Mgr] = 'S5', [Regional Sales Mgr Name],
if([Regional Sales Mgr] = 'S9', [Regional Sales Mgr Name])))))) AS [Regional Sales Mgr Name 2]
resident [Customer Good Sales DataFinalRegional];

Drop table [Customer Good Sales DataFinalRegional];

Rename table tmpTable to [Customer Good Sales DataFinalRegional];

 

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

You may use match() instead of a nested if-loop, like:

if(match(Mgr, 'S1', 'S2', ...), Name2)

and also a mapping-approach would be possible.

Beside this you may avoid this stuff by including the condition within the chart-expression and not within a dimension, like:

sum({< Mgr = {'S1', 'S2', ...}>} value)

View solution in original post

3 Replies
marcus_sommer

You may use match() instead of a nested if-loop, like:

if(match(Mgr, 'S1', 'S2', ...), Name2)

and also a mapping-approach would be possible.

Beside this you may avoid this stuff by including the condition within the chart-expression and not within a dimension, like:

sum({< Mgr = {'S1', 'S2', ...}>} value)

ShawnatQlik
Employee
Employee

I would probably do this:

1. Load just the rows you need into a new table, RSM2.

then you could just have it auto join the model on "Regional Sales Mgr" and use the new feild "Regional Sales Mgr Name 2" in your chart. Or if you do not need the other values on the original table, drop and rename as you did above.

RSM2:
LOAD
"Regional Sales Mgr",
"Regional Sales Mgr Name" as "Regional Sales Mgr Name 2"
resident [Customer Good Sales DataFinalRegional]
Where match("Regional Sales Mgr",'S1','S2','S3','S4','S5','S9');

williamandersson
Partner - Contributor III
Partner - Contributor III
Author

Thank you @marcus_sommer and @ShawnatQlik for your input, knew there were better ways of handling this. Will try your solutions! 🙂