topic For Each Loop not working in QlikView

For Each Loop not working

wfortuny — Sat, 27 Feb 2021 16:54:22 GMT

For Each Loop not working:

I am trying to load in data from a single field into a value list to use in a For Each Loop. I can get the data loaded, but then when trying to use in the for each loop I keep getting the “Field ‘Dataset’ not found”.

Here’s the section of code I’m working with:

[Databases]:

NoConcatenate

SELECT DATABASE

FROM SYSTEM.ADMIN.DATABASE

where (DATABASE <> 'FIELD_INT' and DATABASE LIKE 'FIELD%');

TempDatabases:

load concat(Chr(39) & DATABASE & Chr(39), ',') as concatenatedDatabases resident [Databases];

let Dataset = peek('concatenatedDatabases');

trace $(Dataset);

For each fy in FieldValueList('Dataset')

[EMPS]:

SELECT DISTINCT

AA.EMP_ID_NO,

AA.EMP_LNAME,

AA.EMP_FNAME,

(AA.EMP_LNAME || ', ' || AA.EMP_FNAME) AS EMP_NAME

FROM $(fy).TABLE AA

WHERE AA.LOCATION_ID = '42';

After I run the code, I get the error. If I run only this piece:

[Databases]:

NoConcatenate

SELECT DATABASE

FROM SYSTEM.ADMIN.DATABASE

where (DATABASE <> 'FIELD_INT' and DATABASE LIKE 'FIELD%');

TempDatabases:

load concat(Chr(39) & DATABASE & Chr(39), ',') as concatenatedDatabases resident [Databases];

let Dataset = peek('concatenatedDatabases');

trace $(Dataset);

I get the desired output for Dataset, which is the list of values I want to loop with:

Lines fetched: 1

'FIELD06','FIELD07','FIELD08','FIELD09','FIELD10','FIELD11'

Please help.

Re: For Each Loop not working

Saravanan_Desingh — Sat, 27 Feb 2021 17:16:45 GMT

So Dataset is a variable right? Try this,

For each fy in FieldValueList($(Dataset)) [EMPS]: SELECT DISTINCT AA.EMP_ID_NO, AA.EMP_LNAME, AA.EMP_FNAME, (AA.EMP_LNAME || ', ' || AA.EMP_FNAME) AS EMP_NAME FROM $(fy).TABLE AA WHERE AA.LOCATION_ID = '42'; NEXT

Re: For Each Loop not working

wfortuny — Sat, 27 Feb 2021 17:18:15 GMT

I tried that, but of course right after I posted this question I found the solution. In the call to the for each loop I changed to this:

For each fy in $(Dataset)

and that worked, not sure why the "FieldValueList()" didn't work?

Re: For Each Loop not working

Saravanan_Desingh — Sat, 27 Feb 2021 17:19:51 GMT

Oh yes. You are right. My bad.

Dataset is not a Field. Its a variable. So, FieldValueList won't work here. Nice catch.

Re: For Each Loop not working

wfortuny — Sat, 27 Feb 2021 17:21:00 GMT

Only took me about 6hrs to find this, darn it. 🤣

Thank you for the quick support!

Re: For Each Loop not working

Saravanan_Desingh — Sat, 27 Feb 2021 17:21:39 GMT

you welcome

Re: For Each Loop not working

Saravanan_Desingh — Sat, 27 Feb 2021 17:34:20 GMT

One more thought. If you still want to use the function, you can try using the Field directly.

FieldValueList('DATABASE')

Re: For Each Loop not working

wfortuny — Sat, 27 Feb 2021 17:42:42 GMT

oh man, of course, that worked too! Thank you.👌