Performing a join on data loaded from SQL and Excel

[Anonymous]

Hello Everyone ;

I'm trying to load two tables into a Qlikview document and perform a join. I normally do this with two SQL tables and have no issues doing so. However the new table I'm loading is coming from Excel and I'm having trouble with the sytax for this.

I tried just loading the two tables but for some reason it does not load the data from table 1 if there is no corresponding data in table 2. I show a basic sample below.

I'd like to join on CostCenter - can anyone kindly help me with the correct syntax. I think I need to use the resident command but can't seem to get it to work.

Many Thanks,

Simon

StockShortages:
ODBC CONNECT TO [Excel Files;DBQ=D:\Data\My Documents\Qlikview\Short Report.xls];
SQL SELECT
CostCenter,
Period,
`Over/Short`,
`Net Sales`
FROM `D:\Data\My Documents\Qlikview\Short Report`.`'By Store$'`;

SQL SELECT

a.CostCenter, a."Number of Incidents"
FROM RPS_Main.dbo.v_IncidentReporting a
WHERE a."Case Year" >= '2005';

[#2]

Hi,

Join belongs to qlikview script function's , so we must use load before sql select... . That's allow us to use join .

So try this

StockShortages:

ODBC CONNECT TO [Excel Files;DBQ=D:\Data\My Documents\Qlikview\Short Report.xls];

load CostCenter,
Period,
`Over/Short`,
`Net Sales` ;

SQL SELECT
CostCenter,
Period,
`Over/Short`,
`Net Sales`
FROM `D:\Data\My Documents\Qlikview\Short Report`.`'By Store$'`;

join (StockShortages) load

CostCenter, "Number of Incidents";

SQL SELECT

a.CostCenter, a."Number of Incidents"
FROM RPS_Main.dbo.v_IncidentReporting a
WHERE a."Case Year" >= '2005';

I'm waiting for your feed-back

[Anonymous]

I don't understand this fully? Is the idea that you must LOAD all field names that you use in the SQL SELECT statement first? If so that seems lik a lot of replication. My sample I show is a shortened version for the purposes of saving space here, my actuall SELECT statement contains many many field names, using aliases etc.

I also don't understand why the "Number of Incidents" is included in the Join stmt. I want to join on the field CostCenter. My previous experience with joins is solely with SQL so perhaps I'm missing something here.

Cheers,

Simon

[#4 · Accepted solution]

The way i've done this in the past is to load the data frm SQL into a table as you have done, then load the excel data into a table temporarily, perform a join to add the excel fields to the original SQL data, then to drop the excel data table - i've used a left join below, but you can use whichever type of join you need, this should add the excel field to the associated records in your StockShortages table... i reckon. The join is performed automatically by Qlikview as long as the two fields that you are meant to join on are named the same.. in this case CostCenter and CostCenter... if they aren't then just rename them when you load the data. Hope this helps

e.g.

StockShortages:

ODBC CONNECT TO [Excel Files;DBQ=D:\Data\My Documents\Qlikview\Short Report.xls];
SQL SELECT
CostCenter,
Period,
`Over/Short`,
`Net Sales`
FROM `D:\Data\My Documents\Qlikview\Short Report`.`'By Store$'`;

TempTable:

SQL SELECT

a.CostCenter, a."Number of Incidents"
FROM RPS_Main.dbo.v_IncidentReporting a
WHERE a."Case Year" >= '2005';

LEFT JOIN (StockShortages)

Load *

RESIDENT TempTable;

DROP TABLE TempTable;

[#5]

hey there

in qlikview do like this ..

go to the (table file) press ok select your file and load your tables ...

after you load file follow this sysntax

join [ (tablename ) ]( loadstatement | selectstatement )

hope this will help you

Regards,

Abdalla

[#6]

yes , i suggest the same thing in my previous reply

[Anonymous]

Thanks very much, I played with it some more and got it working.

Cheers,

Simon