Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Is this in the script?
You can use three elements of script functionality to achieve this
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
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
@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
@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
Is LEF a table? Does this mean that LEF.secd_qtn_7... etc are fields and not variables?
Pi
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.
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?
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
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