problem with many joins on same table

[Anonymous]

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!

[giakoum]

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

[jonathandienst]

Hi

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

[marcus_sommer]

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

[Anonymous]

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,

     Email

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email1);

Left Join

LOAD Distinct

     ID,

     Email

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email2);

[marcus_sommer]

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

[Anonymous]

What would be the syntax to do so?

Thx

[marcus_sommer]

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

[Anonymous]

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.

[#10 · Accepted solution]

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,

     Email

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email1);

Concatenate (Email)

LOAD Distinct

     ID,

     Email

FROM

[..\External Data\email.xlsx]

(ooxml, embedded labels, table is email2);

Join (workers)

Load * Resident Email;

DROP Table Email;