topic Re: If statement for multiple variables in QlikView

If statement for multiple variables

QlickySense — Wed, 07 Jul 2021 08:26:26 GMT

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?

Re: If statement for multiple variables

PiEye — Thu, 08 Jul 2021 08:03:05 GMT

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

Re: If statement for multiple variables

QlickySense — Fri, 09 Jul 2021 08:00:52 GMT

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

Re: If statement for multiple variables

PiEye — Fri, 09 Jul 2021 10:23:51 GMT

@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

Re: If statement for multiple variables

QlickySense — Fri, 09 Jul 2021 13:26:54 GMT

@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

Re: If statement for multiple variables

PiEye — Fri, 09 Jul 2021 15:00:50 GMT

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

Re: If statement for multiple variables

QlickySense — Mon, 12 Jul 2021 10:14:42 GMT

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.

Re: If statement for multiple variables

QlickySense — Tue, 13 Jul 2021 07:00:55 GMT

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?

Re: If statement for multiple variables

PiEye — Tue, 13 Jul 2021 08:26:32 GMT

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?

Re: If statement for multiple variables

QlickySense — Tue, 13 Jul 2021 08:53:11 GMT

Yess!! Thank you very much
Could you give some advice from which sources it is best to learn Qlik? I find it much harder to learn python/R or other languages - syntax wise as well as what certain functions do to UI/data model

Re: If statement for multiple variables

PiEye — Tue, 13 Jul 2021 09:01:06 GMT

Hi, have just sent a PM - let me know if you get it!

Basically, there's a lot of good stuff out there, we're lucky to have such a good community around us. I would recommend a basic tutorial first, then try a couple of projects on your own.

What I wouldn't recommend are classroom based courses, my experience of them has been that you may as well learn at your own pace but it has also depended on what you are trying to learn (for tutorials and getting started, I dont think its necessary)

Re: If statement for multiple variables

QlickySense — Tue, 13 Jul 2021 10:26:26 GMT

Yeah, replied to you 🙂

I have one more question - this one seems to not be applicable to concatenated variable? Just tried and it doesn't seem to work. I guess its need to be mapped to variables from which concatenation starts?

Re: If statement for multiple variables

PiEye — Tue, 13 Jul 2021 12:11:28 GMT

True - it will apply to the whole field as it's loaded in. So you might need to load the fields first, then concatenate.