Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
OysteinT
Contributor III
Contributor III

LOAD script with two libary connects and join?

Hi

I'm trying to create a load script where I load data from two different connections. That in it self works if I load them into seperate tables, I can have them in two different tables. 

But if I try to load it into one table using left join (keeping the first table, and then adding the extra data into new fields on it) I get an error I'm too green to understand. 

Load script:

 

LIB CONNECT TO 'CDW_Prod';
[Time]:
LOAD PunchScheduleId,
    EmployeeId,
    RegisteredTimeId,
    PunchServiceDeliveryAreaId,
    PunchProfitCenter,
    PunchAgreement,
    PunchInTimeDate,
    PunchOutTimeDate,
    HoursWorked,
    CoorCompany,
    Country,
    Trim(left(Trim(mid([EmployeeId], 3 + 1)), 6)) AS [empno],
	Trim(left(Trim(mid([PunchProfitCenter], 3 + 1)), 4)) AS [PunchProfitCenter2];
SQL SELECT PunchScheduleId,
    EmployeeId,
    RegisteredTimeId,
    PunchServiceDeliveryAreaId,
    PunchProfitCenter,
    PunchAgreement,
    PunchInTimeDate,
    PunchOutTimeDate,
    HoursWorked,
    CoorCompany,
    Country
FROM CDW_PROD_DB.FactRegisteredTime WHERE Country = 'NO';

LIB CONNECT TO [Microsoft_SQL_Server (network_querty)];
left Join (Time)
[Employee]:
LOAD
	[empno],
	IF([info_5]='TT','Timelønn',If([info_5]='TM','Timelønn',if([info_5]='T','Timelønn',IF([info_5]='TV','Timelønn','Fastlønn'))))
	AS [Lønnstype];
SELECT
	empno, 
	"info_5", 
FROM "promark_dw_prod3".dbo."dw_employee";

 

 

The error I get is:

The following error occurred:
Field 'empno' not found
 
The error occurred here:
SELECT empno, "info_5", FROM "promark_dw_prod3".dbo."dw_employee"
 
What silly mistake am I doing? 😞 
1 Solution

Accepted Solutions
OysteinT
Contributor III
Contributor III
Author

 Seems my problem was more the placement of the [Employee]: statement.

When I moved it from above the LOAD to above the SELECT statement it seemed to work as I want.

 

left join 
LOAD empno as [EmployeeId2], 
	info_5, 
	payrollprofid;

[Employee]:
SELECT empno,
	"info_5",
	payrollprofid
FROM "promark_dw_prod3".dbo."dw_employee";

 The above works as I want it. Yay. 

View solution in original post

5 Replies
uroboros
Creator
Creator

Hi, in fact, you can join two or more tables, the error that you have it's not triggered by a Qlik limitation, it triggered because you have a bad field call, maybe one of your field have a diferent name on a original table name. 

Regards! 

Vegar
MVP
MVP

You are missing a 'SQL'.

 

SQL SELECT
empno,
"info_5",
FROM "promark_dw_prod3".dbo."dw_employee";

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Qlik1_User1
Creator III
Creator III

Check if below script is working in SQL or not, if yes than just run this script in qlik and than check where is the issue.

SELECT
empno,
"info_5",
FROM "promark_dw_prod3".dbo."dw_employee";

OysteinT
Contributor III
Contributor III
Author

 Seems my problem was more the placement of the [Employee]: statement.

When I moved it from above the LOAD to above the SELECT statement it seemed to work as I want.

 

left join 
LOAD empno as [EmployeeId2], 
	info_5, 
	payrollprofid;

[Employee]:
SELECT empno,
	"info_5",
	payrollprofid
FROM "promark_dw_prod3".dbo."dw_employee";

 The above works as I want it. Yay. 

View solution in original post

OysteinT
Contributor III
Contributor III
Author

Hi

The SQL is not needed, when I add data using the Wizard from a SQL server it does not add it, and it does not stop the scripts.