problem with variable in for each loop

Tue, 10 Aug 2010 16:04:07 GMT

Hi,

my issue is to load data from differenz companies dynamically. The exisiting company names can be loaded from the data source.

Then I write all the company names into one field, which I wanted to use as variable for the "for each..next" loop.

But my variable seems to be wrong.

See my code below, which is not working:

unqualify *;

Companies:

LOAD Name,

autonumber(Name) as Linenumber,

'1' as Gruppierung;

SQL SELECT Name

FROM "Database".dbo.Company;

Temp:

LOAD Gruppierung, concat(Name, ',') AS Namensliste resident Companies GROUP BY Gruppierung;

LET currField = 'Namensliste';

LET vCOMPANIES= FieldValue('$(currField)', 1);

unqualify*;

FOR EACH vCOMPANIES in $(vCOMPANIES)

Customer:

LOAD

'$(vCOMPANIES)' as Company,

"No_",

Name;

SQL SELECT *

FROM "Database".dbo."$(vCOMPANIES)$Customer";

store Customer into . /qvd/Customer_$(vCOMPANIES).qvd;

If I fill the vCOMPANIES variable manually like this (

SET

vCOMPANIES='Company XX','Company YY' ) it works. The only difference are the ' ' in the string.

But I need to load the companies dynamically from the source db.

jacekvsp — Thu, 21 Apr 2011 13:15:04 GMT

You got it right, the only difference is apostrophe (') or chr(39), try adding it like this:

LOAD Gruppierung, concat(chr(39)&Name&chr(39), ',') AS Namensliste resident Companies GROUP BY Gruppierung;

topic problem with variable in for each loop in QlikView