Joining tables within a for loop

nsm1234567 — Mon, 14 Jan 2013 08:52:56 GMT

Hi There,

I'm battling a bit with the below script. I have a nested for loop which I'm using to retrieve data from each server. Thanks to some help I received, this now works. The problem now comes with the text I've marked in red, where I'm trying to add data from some additional tables. What happens is that when it cycles through the loop the second time, it now creates a table called "Data-1" and then does a synthetic join (not ideal).

Is there a way to avoid this? I've tried doing the join at various other points without success. The reason I'm using the join is that not all departments in the department table receive text messages with client scores.

For each CNTR in 20, 40

if $(CNTR) = 20 then

set zServer = COMPANY1 ;

ODBC CONNECT32 TO COMPANY1 (XUserId is acCHQAFJSKZCGSdJODbSHID, XPassword is UROCRAFGJSbKTaJOBTbGWSNL);

ENDIF

if $(CNTR) = 40 then

set zServer = COMPANY2 ;

ODBC CONNECT32 TO COMPANY2 (XUserId is GdPSWAFJSKZCGSdJODbSHbD, XPassword is EQTDcAFGJSbKTaJOBTbGWSdN);

ENDIF

For each Score in 'Excellent', 'Good','Bad'

[DATA]:

LOAD recno() as [ID INLINE],

"Commslog_Key",

"Comm_Crt_Date",

"Comm_Comm_Type",

"Comm_Area_Cde",

if(Comm_Area_Cde = 'C', 'SMS linked to a claim number','SMS linked to a policy number') as "SMS",

"Comm_Status",

"ReferenceNo",

"Usr_Full_Name" as "Username",

"Comm_Reference_Cde",

"Comm_Report_Cde",

"Comm_Template",

"Comm_Recipients",

"In_Received_Date",

"In_Message",

'$(Score)' as Score,

'$(zDate)' as Today,

'$(StartDate)' as StartDate,

'$(EndDate)' as EndDate,

'$(zServer)' as Server;

SQL SELECT *

FROM Softsure.dbo."View_SMSInBox"

where Comm_Crt_Date >= '$(StartDate)' and Comm_Crt_Date <= '$(EndDate)'

AND Comm_Report_Cde = '17'

and SOUNDEX(In_Message) = SOUNDEX('$(Score)');

Join(DATA)

UserTable:

LOAD

"Usr_Full_Name" as "Username",

"Usr_Department_Cde" as "DepartmentCode";

SQL SELECT *

FROM Softsure.dbo."View_User_Roles" where Usr_Department_Cde IS NOT NULL;

Join(DATA)

DepartmentTable:

//-------- Start Multiple Select Statements ------

LOAD "Dept_Key" as "DepartmentCode",

"Dept_Name",

"ManagerName";

SQL SELECT *

FROM Softsure.dbo."View_Departments";

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

Re: Joining tables within a for loop

Qlik_Olli — Fri, 03 Sep 2021 09:39:51 GMT

You can use the loop variable and use it as a counter for your two tables to link your tables together via this number after the loop.

topic Re: Joining tables within a for loop in QlikView

Joining tables within a for loop

Re: Joining tables within a for loop