Left Join

[#1]

Hi there,

I spend a lot of time looking for a solution to enrich a table but I cannot do what I want.

Here is what I Made:

Table1:

LOAD

MyID,

Task;

FROM [MyLib/Table1.xlsx]

(ooxml, embedded labels, header is 189, table is Request);

Left Join

LOAD

[MyID],

[MyFlag];

LOAD

[MyID],

1 AS [MyFlag]

FROM [MyLib/Table2.xlsx]

(ooxml, embedded labels, header is 24, table is Request);

Table1 looks like this:

MyID     |     Task         

AAA      |        1                 

AAA      |        2                

AAA      |        3                

BBB      |        1                

BBB      |        2                 

CCC      |        1               

DDD      |        1

Table2 looks like this:

MyID     |     Task      |       MyFlag

AAA      |        2         |            1

BCD      |        1         |           1

BBB      |        2         |            1

ZZZ      |        1         |           1

XSD      |        1         |          1

And I have this result:

MyID     |     Task      |       MyFlag

AAA      |        1         |           null

AAA      |        2         |             1

AAA      |        3         |           null

BBB      |        1         |           null

BBB      |        2         |             1

CCC      |        1         |           null

DDD      |        1         |           null

And I want to have:

MyID     |     Task      |       MyFlag

AAA      |        1         |             1

AAA      |        2         |             1

AAA      |        3         |             1

BBB      |        1         |             1

BBB      |        2         |             1

CCC      |        1         |           null

DDD      |        1         |           null

And I do not understand why it does not work because I did not put any restriction/filter on Task Code...

Thanks a lot for your help

Cheers

[sunny_talwar · Accepted solution]

May be you don't want to join on Task and only on MyID

Table1:

LOAD * INLINE [

    MyID, Task

    AAA, 1

    AAA, 2

    AAA, 3

    BBB, 1

    BBB, 2

    CCC, 1

    DDD, 1

];

Left Join(Table1)

LOAD MyID,

  MyFlag,

  Task as Task_Temp;

LOAD * INLINE [

    MyID, Task, MyFlag

    AAA, 2, 1

    BCD, 1, 1

    BBB, 2, 1

    ZZZ, 1, 1

    XSD, 1, 1

];

[#3]

Hey! thanks for replying.

In fact this was just an example I am loading big tables from excel files and I can't copy them directly in the code XD.

And if you look at my code I am not joinning on Tasks... that's why I am a bit perplex... I am joinning only on 'MyID'

Cheers

[#4]

I find a way! in fact I was not using right field naming.

Thanks for help