Create field from variable

nsm1234567 — Sat, 12 Jan 2013 16:42:29 GMT

Hi There, I'm having an issue running the below script. I have identical tables to retrieve data from which lie on two different servers. To make the scripting more manageable, I've created a "for" loop which, in theory, would connect to "Company1" the first time round and then "Company2" the second time round.

The problem I have is that when I start loading the data, using "$(zServer)" I get "Field not found - <Company1>" as an error. If instead I do the load as:

LOAD recno() as [ID INLINE ],

'Company1' as [Server],

I don't get any problems.

Can anyone see where I'm going wrong here? Any help would be appreciated

For each CNTR in 20, 40

if $(CNTR) = 20 then

set zServer = 'Company1';

ODBC CONNECT TO COMPANY1 (XUserId is DdBFdAFJSKZCGSdJODbSHSC, XPassword is eBVBYAFGJSbKTaJOBTbGWSZN);

ENDIF

if $(CNTR) = 40 then

set zServer = 'Company2';

ODBC CONNECT TO COMPANY2 (XUserId is DdBFdAFJSKZCGSdJODbSHSC, XPassword is eBVBYAFGJSbKTaJOBTbGWSZN);

ENDIF

SMSTABLE:

LOAD recno() as [ID INLINE ],

$(zServer) as [Server],

"Comm_Crt_Date",

"Comm_Report_Cde",

"Comm_Template",

"In_Message";

SQL SELECT *

FROM Softsure.dbo."View_SMSInBox"

where CONVERT(date,Comm_Crt_Date) >= '2013-01-10'

AND Comm_Report_Cde = '17'

and (SOUNDEX(In_Message) = SOUNDEX('Excellent')

or SOUNDEX(In_Message) = SOUNDEX('Good')

or SOUNDEX(In_Message) = SOUNDEX('Bad')) ;

//-------- End Multiple Select Statements ------

Re: Create field from variable

Gysbert_Wassenaar — Sat, 12 Jan 2013 17:01:25 GMT

Try adding single quotes around the variable:

SMSTABLE:

LOAD recno() as [ID INLINE ],

'$(zServer)' as [Server],

"Comm_Crt_Date",

"Comm_Report_Cde",

"Comm_Template",

"In_Message";

Re: Create field from variable

nsm1234567 — Sat, 12 Jan 2013 17:13:38 GMT

That worked! Thanks for the help.

topic Re: Create field from variable in QlikView

Create field from variable

Re: Create field from variable

Re: Create field from variable