use CONCATENATED variables in script WHERE clause to refer to fields

Eddy — Sat, 16 Nov 2024 19:29:03 GMT

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:
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?

Re: use CONCATENATED variables in script WHERE clause to refer to fields

Vegar — Tue, 10 Dec 2019 12:56:34 GMT

Change your where statement from

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

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

Re: use CONCATENATED variables in script WHERE clause to refer to fields

Eddy — Tue, 10 Dec 2019 13:47:55 GMT

Thank you very much, that works!

topic Re: use CONCATENATED variables in script WHERE clause to refer to fields in App Development

use CONCATENATED variables in script WHERE clause to refer to fields

Re: use CONCATENATED variables in script WHERE clause to refer to fields

Re: use CONCATENATED variables in script WHERE clause to refer to fields