topic Re: JOIN file LOADed from Excel with SQL files in QlikView

JOIN file LOADed from Excel with SQL files

Wed, 11 Sep 2013 16:10:48 GMT

I have a large SQL database, and one file of information in Excel.

I load the file as follows:

BP:

[User ID],
     UPPER( [Email Address]) AS "Email Address",
     DupEmail,
     Company As "BPCompany"
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Then, I want to join it to an existing SQL file

SQL

Select

Users.USC_Email,

Company.COC_Company

FROM

WA.dbo.Users

LEFT OUTER JOIN

WA.dbo.COMPANY

ON US_CO_ID=COC_UID

(Note: Up to here, this works fine, but when I add this...)

LEFT OUTER JOIN

ON COC.Company = BPCompany

I get an error message "Invalid object name 'BP'"

How do I specify the Excel table so that it will join with the others?

Thanks,

Kevin

Re: JOIN file LOADed from Excel with SQL files

rbecher — Wed, 11 Sep 2013 16:43:19 GMT

Hi Kevin,

how could your database know about the Excel file, or the loaded data from it? You have to load the data from the SQL Select statement into QlikView and do a LEFT JOIN LOAD. Unfortunately you cannot filter the data in the SQL Select statement by BPCompany..

- Ralf

Re: JOIN file LOADed from Excel with SQL files

rbecher — Wed, 11 Sep 2013 16:51:22 GMT

..or better use exists(BPCompany, COC.Company) to load only the companies from the Excel. But the SQL Select will still return all companies.

Re: JOIN file LOADed from Excel with SQL files

Wed, 11 Sep 2013 17:18:00 GMT

Hi Ralf,

Perhaps I didn't make this clear - the SQL I posted IS IN MY QLIKVIEW script.

So, first I load the data from the external ODBC database using SQL Selects,

and then I load the data from the Excel file using LOAD. I thought that QV then

keeps ALL the files in memory. (Note: I already do a QV associative join by

aliasing one of my db's fields to a BP field, and it works.)

What is this LEFT JOIN LOAD you speak of? I cannot find anything about in the

QV Help file. And please - I know it's a pain, but would you mind typing out the

complete syntax, because QV Help is next to useless. e.g.is the syntax:

SQL Select

(fields)

FROM

WA.dbo.Users

LEFT JOIN LOAD (

fields)

FROM

c:\directory\BP excel file, sheet, etc.

Thanks,

Kevin

Re: JOIN file LOADed from Excel with SQL files

rbecher — Wed, 11 Sep 2013 17:24:17 GMT

The SQL Select statement runs inside the database. There is no way to join data from outside the database.

What I meant was a load with LEFT (join). Search help for 'left'.

But in your case I suggest to do this, a preceding LOAD with exists:

LOAD * Where exists(BPCompany, Company.COC_Company);

SQL Select

Users.USC_Email,

Company.COC_Company

FROM

WA.dbo.Users

LEFT OUTER JOIN

WA.dbo.COMPANY

ON US_CO_ID=COC_UID;

- Ralf

Update: the SQL Select was incomplete

Re: JOIN file LOADed from Excel with SQL files

Clever_Anjos — Wed, 11 Sep 2013 17:31:32 GMT

Simply you can´t do such type of thing

As ralph.beck, said, the SQL runs outside QlikView "world".

Is there a trick anyway

1) Create a string with all companies

LOAD

concat(BPCompany,',') as AllCompanies

resident BP;

LET vList = peek('AllCompanies');

2) Use that list as a filter to your query'

SQL

Select

Users.USC_Email,

Company.COC_Company

FROM

WA.dbo.Users

LEFT OUTER JOIN

WA.dbo.COMPANY

ON US_CO_ID=COC_UID

WHERE COC.Company in ($(vList));

Re: JOIN file LOADed from Excel with SQL files

rbecher — Wed, 11 Sep 2013 17:40:44 GMT

Interesting, but you could hit a limit if you have a long list of companies.

Btw. I'm not ralph.beck, 😉

Re: JOIN file LOADed from Excel with SQL files

Clever_Anjos — Wed, 11 Sep 2013 19:40:11 GMT

Sorry Ralf Becher

Re: JOIN file LOADed from Excel with SQL files

rbecher — Wed, 11 Sep 2013 19:43:38 GMT

..btw an SQL IN Clause usually is structured as a list of values like this:

WHERE COC.Company in ('ComapnyA','CompanyB','CompanyC')

This quoting must take into account too. Not very easy with the Concat() function and a variable...

Re: JOIN file LOADed from Excel with SQL files

Clever_Anjos — Wed, 11 Sep 2013 20:34:35 GMT

You right again, but we can do :

LOAD

concat(BPCompany,chr(39)&','&chr(39)) as AllCompanies

resident BP;

LET vList = chr(39)& peek('AllCompanies') & chr(39);