Joining more than two tables

[#1]

Hi everybody,

I need some help in how to join more than two tables. I need to, for example:

1-. Create a table with the inline assistant, consisting of USERNAME and ACCESS.

2-. JOIN that table with various others from, say an Excel file, which contains the passwords. Imagine I need each user of the document to renew his/her password through an excel file which must only be accessed/modified by them.

I tried that operation and it seems to be working for the first JOIN, but then; when I call the other tables, they just don't join as I was expecting. The result it's something as if I was concatenating them.

This is the code:

Accesos:
LOAD * INLINE [
 ACCESS, USERNAME
 ADMIN, ADMIN
 ADMIN, SA
 USER, SA
 USER, AA
 USER, BB
 USER, CC
];
ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\XXXX\My Documents\Cambio de Password Usuario de QV Client MAT.xls];
OUTER JOIN (Accesos)
Load USERNAME,
 [PASSWORD]
FROM  (biff, embedded labels, table is [ActPasswords $]);
OUTER JOIN (Accesos)
Load USERNAME,
 [PASSWORD]
FROM  (biff, embedded labels, table is [ActPasswords2 $]);

The result:

USERNAME	ACCESS	PASSWORD
AA	USER	0
ADMIN	ADMIN
BB	USER
CC	USER
CC		0
SA	ADMIN
SA	USER

See what I mean?

The first excel sheet contains the password (0) for the user AA. So the script is doing what I want: Add the password from that excel sheet.

But for the "CC" user, the script is just adding a new row containing the password. It's not joining the information with the password to the "Accesos" table.

I hope someone can help me, It seems to be a database 101 lesson, but I'm pretty new with this stuff. Maybe I'm not using the right sentence, but I've tried with all kind off JOIN, INNER, KEEP, etc.

Thanks in advance,

Javier

[#2]

pretty new to Qlikview myself, but I'm pretty sure you can just drop the 'join' part alltogether. QlikView is going to concatenate all of your password files together (because they have the same number of fields, and each field has the same name), and then join them by name to your user table (assuming the field names are the same). You shouldn't need to explicitly join anything.

[msteedle · Accepted solution]

You don't want to be executing two separate joins here, and the use of outer joins, specifically, is how you are ending up with multiple rows per user. You may want to read about what outer joins are, but your first one is adding the password column to the Access table, and the second one is doing something else.

Concatenate load all of your spreadsheets into one temp table, then left join that temp table to your Access table. You can then drop the temp table.

[msteedle]

Fry,

The Access table has a different structure than the password spreadsheets, so any concatenation would have be explicit, using the CONCATENATE load statement prefix.

We need joins here, anyway. Concatenation would add rows to this table, but we want the same number of rows as the original Access table inline load, only with the addition of a field containing password values.

[#5]

Thank you very much, Fry and Michael.

We applied just what Michael suggested, and it seems to be working. Sure I need to read more about joins!

Anyway. I appreciate your help.

Best regards,

Javier