Solved: If statement for multiple variables - Qlik Community

QlickySense · ‎2021-07-07

Hello,

I have variable A, B, C, D. They all got values ranges from 1 to 4, and all these values have its description

What I would like to acheieve is to keep the var names A, B, C, D but change their labels to 1 - Ok 2- Not bad; 3-Bad, 4-Very bad

Yes I can do that separately for each, but its time consuming because i got plenty of these

I was wondering about something like (for all values but ill present for one) IF(A OR B OR C OR D = 1, 'Ok'). However it does not work because it didnt set it "AS" variable. I'd like to keep A,B,C,D names but change only value because then I have good legend, not 1-4 entries

Any suggestions?

PiEye · ‎2021-07-13

Ah! This changes everything! I was assuming that a,b,c... etc are variables, but they are fields.

In qlik, you can't apply a function to a single field, there is no equivalent of ADD or UPDATE like you get in other languages.

However there's a hand piece of functionality called mapping tables which might make this easier. Please look them up, they are really useful but in essence they are a form of temporary table with two fields- and input and an output that can be used to transform other values.

You can actually apply the transfrom in a function (applymap () ) or even to a list of incoming fields.

In your example, we would do something like this:

MapScoreValues:

MAPPING

LOAD * INLINE [

In, Out

1,1 - Ok'

2,2- Not bad

3,3-Bad

4,4-Very bad];

Map LEF.secd_qtn_2, LEF.secd_qtn_3, LEF.secd_qtn_4 USING MapScoreValues;

NewTable:

LOAD

ky_LEF,

LEF.secd_qtn_2,

LEF.secd_qtn_3,

LEF.secd_qtn_4

.....

The first statement "MAPPING LOAD assigns the table as a mapping table.

Then, we tell qlik to apply this to the list of fields using MAP... USING

The last statement loads the fields - they will now be transformed as per the mapping table.

Does this work?

Pi

View solution in original post

PiEye · ‎2021-07-08

Is this in the script?

You can use three elements of script functionality to achieve this

A "for" loop, to loop through the variables
A dollar sign expansion, "$(...)" which turns its input into code.
"Pick" is a quick way to assign a value to an integer

This way, we can loop through the variable names, and execute a statement with $() which turns the variables names into code.

SET a=1;
SET b=3;
SET c=4;
SET d=2;

FOR EACH vVarName IN 'a','b','c','d'

LET [$(vVarName)]=Pick([$(vVarName)],'1 - Ok','2- Not bad','3-Bad','4-Very bad');

NEXT

QlickySense · ‎2021-07-09

Hello @PiEye . It looks promising. I've tried to paste that in several places but none of them work - scripts goes without error but nothing has been changed

plus I tried to change FOR EACH vVarName IN 'a','b','c','d' with actual variables without parenthesis and it does not work as well

PiEye · ‎2021-07-09

@QlickySense Might be easier with an example as there could be a few things happening here. Can you show me what you have tried exactly? And what you have modified to - and does it work when you manually set the variables to a number?

Sometimes if they have a space in, it might not pick up the number too

Pi

QlickySense · ‎2021-07-09

@PiEye What I've initially tried was on capture, then I tried to insert it into this LEF table, but nothing succeed. Kept the way on screen, kept the way you wrote with a-d, tried several diff options

Cant give out qvf flie because its sensitive data

I dnt know if what I've done makes any sense

PiEye · ‎2021-07-09

Is LEF a table? Does this mean that LEF.secd_qtn_7... etc are fields and not variables?

Pi

QlickySense · ‎2021-07-12

LEF is a table, LEF.secd_qtn_7 is a field. This has been made due to Qualify function but now I understand it created huge mess.

QlickySense · ‎2021-07-13

Hi,

SET a=1;
SET b=3;
SET c=4;
SET d=2;

FOR EACH vVarName IN secd_qtn7_2.LEF, secd_qtn7_3.LEF

LET [$(vVarName)]=Pick([$(vVarName)],'1 - Ok','2- Not bad','3-Bad','4-Very bad');

doesnt work without Qualify as well 😞 any suggestion?

PiEye · ‎2021-07-13