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

[williamandersson]

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];

[marcus_sommer · Accepted solution]

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]

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]

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