topic Joining 2 fields from 2 different tables in QlikView

Joining 2 fields from 2 different tables

hopkinsc — Tue, 17 Aug 2010 10:41:02 GMT

Hi, i need some help joining 2 fields from different tables.

My tables are as follows

LOAD

StoreNumber,

TransId,

EmployeeId

FROM

C:\QLIKVIEW\Transaction.csv

LOAD

StoreNumber,

EmployeeName

FROM

C:\QLIKVIEW\Employee.csv

I want to create a new field called STOREEMPLOYEE from EmployeeId (from the first table) and EmployeeName (From the second table)

Can anyone tell me how to do thsi please?

Joining 2 fields from 2 different tables

Tue, 17 Aug 2010 11:10:59 GMT

Assuming StoreNumber is the key between these two tables.
EMPTRN:
LOAD

StoreNumber,

TransId,

EmployeeId

FROM

C:\QLIKVIEW\Transaction.csv;
Left join
LOAD

StoreNumber,

EmployeeName

FROM

C:\QLIKVIEW\Employee.csv;

EmpFinal:
Load *,
EmployeeId & EmployeeName as STOREEMPLOYEE
resident EMPTRN;
drop table EMPTRN;

Joining 2 fields from 2 different tables

llauses243 — Tue, 17 Aug 2010 11:29:34 GMT

To use "EmployeedId as STOREEMPLOYEE" in a the next help ...

Joining 2 fields from 2 different tables

jonathandienst — Tue, 17 Aug 2010 11:44:20 GMT

Is what you want even possible?

For any given storenumber, how will you know which EmployeeName relates to which EmployeeID? (Unless there is never more than one employee per StoreNumber)

Jonathan

Joining 2 fields from 2 different tables

hopkinsc — Tue, 17 Aug 2010 11:57:58 GMT

Yes it is possible, It already works great, but with 2 seperate tables (1 for EmployeeId and 1 for Employee Name)

What i inlcuded earlier was only a tiny bit of my script, here is an image of my linked tables...

Joining 2 fields from 2 different tables

hopkinsc — Tue, 17 Aug 2010 12:01:51 GMT

Hi, this will not work as i need to keep the both tables as they have more fields that i require in my script. What i have included in my original post is only a portion of it. There are about 25 fields in the table.

These 2 tables are not linked directly

Joining 2 fields from 2 different tables

jonathandienst — Tue, 17 Aug 2010 12:10:27 GMT

Two separate tables might work - that does not imply that you can combine them into one.

You need to have some way of determining which EmployeeID maps to which EmployeeName - although I did not see an EmployeeID on the model.

One way to test this is to have a sheet showing 3 listboxes: StoreNumber, EmployeeID and EmployeeNumber. Play around withe selections on that and you'll see what I mean. I expect that if you select an employeeID, you'll see more than one name.

I am assuming that there is not some sort of denormalisation in your datasource, or in your load script.

Jonathan

Joining 2 fields from 2 different tables

hopkinsc — Tue, 17 Aug 2010 12:21:28 GMT

Thanks Jonathan, The employeeId is within the POSTransactionHeader, The table image i attached is limited, there are 20+ fields within most of the tables.

An employee name and ID are linked to a store number, So before the end user selects an employee they will first select a store number which then only displays employees relevant to that store.

Joining 2 fields from 2 different tables

jonathandienst — Tue, 17 Aug 2010 12:34:18 GMT

Okay, maybe the example in the initial post misled me. But I see no problem here, or do you need some guidance on the syntax of joining tables?

Joining 2 fields from 2 different tables

hopkinsc — Tue, 17 Aug 2010 12:43:03 GMT

Yes please if you don't mind. Maybe i should have explained it in my initial post a bit better! 🙂

I know how to join fields within the same table, But i have never done it for fields from different tables.

Here is my QVW

I want to link

EmployeeId From POSTransactionHeader with

EmployeeName From StoreEmployee

Your help is very appreciated!

Joining 2 fields from 2 different tables

jonathandienst — Tue, 17 Aug 2010 12:53:41 GMT

Here's a sample script:


MYTABLE:
LOAD EmployeeID,
 StoreNumber,
 ...
FROM POSTTransactionHeader;

INNER JOIN (MYTABLE)
LOAD EmployeeName,
 StoreNumber,
 ...
FROM StoreEmployee;

This will join correctly on StoreNumber, but I am still a little concerned that you are going to get a cross join between EmployeeID and EmployeeName (ie each employeename for each store joined to every employeeID for that store) - which I assume is not what you want.

I would use the list boxes I suggested in an earlier post to check the results.

Jonathan

Joining 2 fields from 2 different tables

jonathandienst — Tue, 17 Aug 2010 13:01:56 GMT

On checking the model you uploaded, you already have the store, ID and name together in the Store Employee table. The key appears to contain the store number and employeeID, and the name is (of course) in the EmployeeName field. Just split out the two values from the key and you have it (SUBFIELD or string manipulation).

No joins required!

Jonathan

Joining 2 fields from 2 different tables

hopkinsc — Tue, 17 Aug 2010 13:07:36 GMT

Sorry Jonathan, I'm not sure what you mean...

Joining 2 fields from 2 different tables

jonathandienst — Tue, 17 Aug 2010 13:37:09 GMT

See Susan Carter in extract from StoreEmployee below:

key value 16695 - 3 (store 16695, employee ID 3)
EmployeeName - Susan Carter

So:

Store number = Num#(Left(%lkStoreEmployee, 5))

Employee ID = Num#(Mid(%lkStoreEmployee, 9, 5))

Employee name = EmployeeName

(Assumes that this data is representative the whole set)

Jonathan

Joining 2 fields from 2 different tables

hopkinsc — Tue, 17 Aug 2010 14:01:47 GMT

Thanks Jonathan for all your help 🙂