Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
wfortuny
Contributor II
Contributor II

For Each Loop not working

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';

NEXT

 

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.

Labels (1)
1 Solution

Accepted Solutions
saran7de
Master
Master

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

FieldValueList('DATABASE')

View solution in original post

7 Replies
saran7de
Master
Master

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
wfortuny
Contributor II
Contributor II
Author

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?

 

saran7de
Master
Master

Oh yes. You are right. My bad.

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

wfortuny
Contributor II
Contributor II
Author

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

Thank you for the quick support!

saran7de
Master
Master

you welcome

saran7de
Master
Master

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

FieldValueList('DATABASE')

View solution in original post

wfortuny
Contributor II
Contributor II
Author

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