JOIN file LOADed from Excel with SQL files

[#1]

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

     BP

    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

[rbecher]

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

[rbecher]

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

[#4]

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

[rbecher]

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

[Clever_Anjos]

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));

[rbecher]

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

Btw. I'm not ralph.beck, 😉

[Clever_Anjos]

Sorry Ralf Becher

[rbecher]

..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...

[Clever_Anjos]

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);