Easy Join Issue

[#1]

Hi, I'd been trying to join a few tables & it just doesn't work propertly!

I've this main table of User and their Acces and on the other hand, I've a file with the password. So, i want to join every file with the password with the main table. It only works with the first file, the rest, it's like not exists.

Here's the code:

ACCESOS:
LOAD * INLINE [
ACCESS, USERNAME
ADMIN, ADMIN
ADMIN, SA
USER, SA
USER, AA
USER, BB
USER, CC
];

LEFT JOIN (ACCESOS) Load
USERNAME,
[PASSWORD]
FROM SA.xls (biff, embedded labels, table is [Sheet1$]);

LEFT JOIN (ACCESOS) Load
USERNAME,
[PASSWORD]
FROM ADMIN.xls (biff, embedded labels, table is [Sheet1$]);


LEFT JOIN (ACCESOS) Load
USERNAME,
[PASSWORD]
FROM BB.xls (biff, embedded labels, table is [Sheet1$]);



I don't know WHY if i'm writing (ACCESOS) don't join it, i really hope isn't something about left, right, outer (i'll be ashame!)

Thxs in advance for your help! i'm confident that's quite easy to resolve! 🙂

[#2]

Hi

When you perform the first join the resulting table will have 3 columns (ACCESS, USERNAME, PASSWORD).
Then, when the second join is performed you already have the password field in the ACCESOS-table. So the join is done on both the USERNAME & PASSWORD field. That's why is doesn't work.
Try somthing like this instead:
tmpPasswords:
load USERNAME, PASSWORD from SA.xls .........;
load USERNAME, PASSWORD from ADMIN.xls ......;
load USERNAME, PASSWORD from BB.xls .....;
left join(ACCESOS) load * from tmpPasswords;
drop table tmpPasswords;

This you concatenate all the password-files into a single table before you do your join. That shold work.

/Fredrik

[#3]

Fredrik, thx u for your answer.

I did what u just told me, and it gave me an error with tmpPasswords, that say:

That the file mypath/tmpPasswords it cannot be open and it appear, too, the line where the join it'd been done: left join (ACCESOS) load * from tmpPasswords;

Why u think this is happening?

Thxs!

[Anonymous]

Maybe the opposite will do the trick??

ACCESOS:
LOAD * INLINE [
 ACCESS, USERNAME
 ADMIN, ADMIN
 ADMIN, SA
 USER, SA
 USER, AA
 USER, BB
 USER, CC
];
LEFT JOIN (ACCESOS)
load USERNAME, PASSWORD from SA.xls .........;
load USERNAME, PASSWORD from ADMIN.xls ......;
load USERNAME, PASSWORD from BB.xls .....;
left join(ACCESOS) load * from tmpPasswords;

Did not try this but might be something

[Anonymous]

the last left join was accidentally copied ... this should not be here!!!

[#6]

Hi A Fuchten,

I really did not understand why to put the left join (ACCESOS) first, but I tried & It doesn't work. It gave me an error for that.

Any idea why the error is in the recomendation of Friedrik?

[msteedle]

This thread is a duplicate of this other one, started yesterday: http://community.qlik.com/forums/p/24106/92302.aspx#92302

The short answer is that you should not be executing a separate join for each spreadsheet. Load all of the spreadsheets into one table, then left join that resident table to the Access table.

[#8]

Hi Michael,

I tried already to load all the spreadsheets into one table and then left join it with the access table, but I got an error with that.

I posted. Can u help me with that?

[#9]

Hi

I made a mistake in my first post. The correct code for the join should be:

left join(ACCESOS) load * resident tmpPasswords;

That should do the trick.

/Fredrik

[#10]

Hi again,

I already got the solution to the last problem, and the error was because of the from statement in the left join clause, i change it for resident (As michael said) and it works as it should.

Thxs to everyone.