topic Re: problem with many joins on same table in QlikView

problem with many joins on same table

Anonymous — Mon, 04 May 2015 13:24:31 GMT

Hello dear community:

I'm having a problem when I try to use many joins on the same table.

For example I concatenate 2 tables, then i have to make 2 left joins to this same table.

The problem is that the second left join doesn't work.

This problem is not only with left joins, but also with inner or outer joins.

Is there a limit of operations that can be made to the same table??

Thanks alot!

Re: problem with many joins on same table

giakoum — Mon, 04 May 2015 13:26:10 GMT

no limit, so you need to elaborate a little and maybe share a sample app

Re: problem with many joins on same table

jonathandienst — Mon, 04 May 2015 13:28:09 GMT

i suggest that you post at least your load script. There are a number of reasons that the second join should fail, but without more information, including in what way it failed, we are only guessing at the reason.

Jonathan

Re: problem with many joins on same table

marcus_sommer — Mon, 04 May 2015 13:29:21 GMT

There aren't technical limits. You have rather a small syntax issue or the data didn't match - the reason could maybe the format, for example: Month with Jan, Feb ... to 1, 2 ...

- Marcus

Re: problem with many joins on same table

Anonymous — Mon, 04 May 2015 13:35:18 GMT

here's what I'm doing:

workers:

LOAD Distinct

ID,

Name,

LastName,

Adress

FROM

[..\External Data\test.xlsx]

(ooxml, embedded labels, table is test1);

concatenate

LOAD Distinct

ID,

Name,

LastName,

Adress

FROM

[..\External Data\test.xlsx]

(ooxml, embedded labels, table is test2);

concatenate

//Alumnos UXXI

LOAD Distinct

ID,

Name,

LastName,

Adress

FROM

[..\External Data\test.xlsx]

(ooxml, embedded labels, table is test3);

Left Join

LOAD Distinct

ID,

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email1);

Left Join

LOAD Distinct

ID,

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email2);

Re: problem with many joins on same table

marcus_sommer — Mon, 04 May 2015 13:43:35 GMT

The second left join will have two keys to main-table then after the first join there si already a field "Email". I suggest you conatenate first both mail-table and join then these table.

- Marcus

Re: problem with many joins on same table

Anonymous — Mon, 04 May 2015 14:00:39 GMT

What would be the syntax to do so?

Thx

Re: problem with many joins on same table

marcus_sommer — Mon, 04 May 2015 14:06:33 GMT

Try:

....

Left Join

LOAD Distinct ID, Email

FROM [..\External Data\email.xlsx] (ooxml, embedded labels, table is email1);

Concatenate (workers)

LOAD Distinct ID, Email

FROM [..\External Data\email.xlsx] (ooxml, embedded labels, table is email2);

- Marcus

Re: problem with many joins on same table

Anonymous — Mon, 04 May 2015 14:16:20 GMT

I tried the code as you put it, but it's adding the information to the end of the table which is creating me repeated entrys.

Re: problem with many joins on same table

Mon, 04 May 2015 14:27:58 GMT

Try this:

workers:

LOAD Distinct

ID,

Name,

LastName,

Adress

FROM

[..\External Data\test.xlsx]

(ooxml, embedded labels, table is test1);

concatenate

LOAD Distinct

ID,

Name,

LastName,

Adress

FROM

[..\External Data\test.xlsx]

(ooxml, embedded labels, table is test2);

concatenate

//Alumnos UXXI

LOAD Distinct

ID,

Name,

LastName,

Adress

FROM

[..\External Data\test.xlsx]

(ooxml, embedded labels, table is test3);

Emai:l

LOAD Distinct

ID,

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email1);

Concatenate (Email)

LOAD Distinct

ID,

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email2);

Join (workers)

Load * Resident Email;

DROP Table Email;

Re: problem with many joins on same table

Anonymous — Mon, 04 May 2015 14:42:49 GMT

Thank you!

Re: problem with many joins on same table

Mon, 04 May 2015 14:46:52 GMT

No problem

Re: problem with many joins on same table

marcus_sommer — Mon, 04 May 2015 16:25:35 GMT

Sorry, this was a mistake from me. Meant was:

....

Left Join (workers)

LOAD Distinct ID, Email

FROM [..\External Data\email.xlsx] (ooxml, embedded labels, table is email1);

Concatenate

LOAD Distinct ID, Email

FROM [..\External Data\email.xlsx] (ooxml, embedded labels, table is email2);

- Marcus