topic Re: Inner join excel and sql in QlikView

Inner join excel and sql

Fri, 27 Sep 2013 13:31:26 GMT

How do I inner join an excel spreadsheet and a sql table. Both have got speciality and consultant fields but I only want the information for the spec and consultants that appear on the excel sheet, so I need to inner join right? (see the screenshot its still bringing through information that doesnt have a spec in the excel but does in the sql)

This is my code:

EXCEL:

LOAD Specialty,

Specialty as "Spec",

Business_Unit,

Planned_Sessions,

Consultant as "cons",

Consultant

FROM

(biff, embedded labels, table is Sheet1$);

SQL:

LOAD    "session_date" as "date",
"session_unique_identifier" as "Session_id",
"session_specialty" as "Spec",
    "scheduled_start",
    "scheduled_finish",
    "session_actual_start",
    "session_actual_finish",
    "session_consultant" as "cons",
    "session_consultant",
    theatre,
    "theatre_area",
    "number_of_notcan_ops",
    "number_of_ops";

SQL SELECT *
FROM Surgery.dbo."vw_thea_sessions_current"

Re: Inner join excel and sql

Anonymous — Fri, 27 Sep 2013 13:35:43 GMT

for inner join, just add this before the sql load

SQL:

inner join (EXCEL) load

...

But from your descriotion folows that you need in fact left join:

SQL:

left join (EXCEL) load

...

Edit: Some explanation:
Inner join loads data that exists in both tables. Left join keeps all from the first table, regardless if it exists or not in the second.

Re: Inner join excel and sql

Fri, 27 Sep 2013 13:42:20 GMT

I agree with Michael.

the correct syntax would be like this

OLEDB CONNECT TO ()

[table name]:

load *

sql select * from table_name;

left join

LOAD col1,col2

FROM

[Your Excel File]

(biff, embedded labels, table is [Plan1$]);

remember that both must have a column name in common for the qlikview associate it.

hope it helps you

Re: Inner join excel and sql

Fri, 27 Sep 2013 14:02:26 GMT

Thanks, could you look at my attachments and tell me where to put the join, they are on 2 different tabs.

I'm a bit confused.

Thanks for your help so far.

Re: Inner join excel and sql

Fri, 27 Sep 2013 14:11:53 GMT

well, I could not understand very well your attachment, but anyway

you have to put the left join on the load script between the 2 loads, like my example

Your excel and your database must have a field with the same name

you can find the documentation for joins on your reference documentation.

I hope it helps you.