Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been trying to join two tables using a LOAD JOIN combination, they are a Calls Table and a Company Table...the Company_Id field is the joining field in both tables....I want to have the Company.Country field available in the Calls table
CALLS:
LOAD
Call_ID,
Last_Updated,
Call_Status,
Company_Id;
SQL SELECT
Call_ID,
Last_Updated,
Call_Status,
Company_Id
FROM
Calls
ORDER BY
Last_Updated;
JOIN (CALLS)
COMPANY:
LOAD
Country;
SQL SELECT
Country
FROM
Company
WHERE
DataBaseName.dbo.Company.Company_Id = DataBaseName.dbo.Calls.Company_Id;
I've tried all variations of
DataBaseName.dbo.Calls.Company_Id
dbo.Calls.Company_Id
Calls.Company_Id
But it always gives me the error
Hello Mark,
You have to do this in two steps:
1.- Load SQL Query per Table and store in qvd file.
2.- Load QVD and do the join same as you have tried.
The script can be like this:
CALLS:
LOAD
Call_ID,
Last_Updated,
Call_Status,
Company_Id;
SQL SELECT
Call_ID,
Last_Updated,
Call_Status,
Company_Id
FROM
Calls
ORDER BY
Last_Updated;
Store CALLS into CALLS.qvd;
Drop Table CALLS;
COMPANY:
LOAD
Company_Id
Country;
SQL SELECT
Country
FROM
Company;
Store COMPANY into COMPANY.qvd;
Drop Table COMPANY;
CALLS:
LOAD *FROM CALLS.qvd;
Join(CALLS)
COMPANY:
LOAD * FROM CAOMPANY.qvd;
The company table join to calls by Company_Id, take care Company_Id most be included in company table to works the join.
Best regards.
Hi Mark,
if you want to make the JOIN in QlikView script (not in the SQL statement) you do not need a join condition like in SQL. Just add the column Company_Id as field in the second LOAD:
JOIN (CALLS)
COMPANY:
LOAD
Company_Id,
Country;
SQL SELECT
Company_Id, Country
FROM
Company;
Or, do the join in the first SQL statement on the database level.
- Ralf
This is what I have been doing up to now but the company table has 270,000 records and really only need the 10,000 of those that are connected to the calls...so I wanted to only load those company records that were actually attached to the calls.
SQL SELECT
a.Country
FROM
Company a
INNER JOIN Calls b on a.Company_Id = b.Company_Id;