topic Easy Join Issue in QlikView

Easy Join Issue

Tue, 05 Jan 2010 15:38:04 GMT

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! 🙂

Easy Join Issue

Tue, 05 Jan 2010 16:02:41 GMT

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

Easy Join Issue

Tue, 05 Jan 2010 16:25:23 GMT

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!

Easy Join Issue

Anonymous — Tue, 05 Jan 2010 16:29:39 GMT

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

Easy Join Issue

Anonymous — Tue, 05 Jan 2010 16:30:53 GMT

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

Easy Join Issue

Tue, 05 Jan 2010 16:41:15 GMT

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?

Easy Join Issue

msteedle — Tue, 05 Jan 2010 17:03:29 GMT

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.

Easy Join Issue

Tue, 05 Jan 2010 17:11:59 GMT

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?

Easy Join Issue

Tue, 05 Jan 2010 17:15:50 GMT

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

Easy Join Issue

Tue, 05 Jan 2010 17:17:16 GMT

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.

Easy Join Issue

Tue, 05 Jan 2010 17:21:11 GMT

yES!

tHnx you!