Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Eddy
Contributor III
Contributor III

use CONCATENATED variables in script WHERE clause to refer to fields

Hello, 

I would like to use two concatenated variables in the WHERE clause - to be able to refer to a different fields in a loop script.  (QVF simple example attached). Inline_load .pngloop.pngvTest.png

 

 

Inline_load:
Load
*
,Key_Projects as %Key
,if(Len(Trim(A))=0,1,0) as A_flag
,if(Len(Trim(B))=0,1,0) as B_flag
,if(Len(Trim(C))=0,1,0) as C_flag;
Load * Inline [
Key_Projects, A, B, C
1, 2, 3,
2, 1, 4, 5
3, 5, ,
4, e, ,
5, , e,
];

I have three fields - A_flag,  B_flag, C_flag. Now I want to create a link table as a loop in script: 

SET vflag = '_flag';

Link:
Load
'A' as %field
,%Key
Resident Inline_load
Where A_flag= 1;


For Each vfield in 'B', 'C'
Concatenate (Link)
Load
'$(vfield)' as %field
,%Key
Resident Inline_load
Where '$(vfield)$(vflag)'= 1;

set vTest = '$(vfield)$(vflag)';
Next vfield

In each loop iteration I want to have a different value in the WHERE clause (B_flag, C_flag), represented by '$(vfield)$(vflag)'.

In the debug mode the concatenated variables seem to be added up to a string (i.e. "B_flag"), but not loaded into the link table. 

 

Can anybody help on that - is this kind of concatenation of variables in the WHERE clause possible to refer to different fields in a loop? 

Labels (3)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Change your where statement from

Where '$(vfield)$(vflag)'= 1; 

to 

Where [$(vfield)$(vflag)]= 1;

 

image.png

View solution in original post

2 Replies
Vegar
MVP
MVP

Change your where statement from

Where '$(vfield)$(vflag)'= 1; 

to 

Where [$(vfield)$(vflag)]= 1;

 

image.png

Eddy
Contributor III
Contributor III
Author

Thank you very much, that works!