Miss left join duplicated rows

[#1]

Hi Friends,

I am in a trouble in left join function.

my two sources and code as below pictures:

what Result I want to get is :

however, actual situation is only first row:

but when my sources change to:

everything goes well:

but in actual situation, I don't have AB2 in second table, what I can do to get the result I need?

[sushil353 · Accepted solution]

Hi,

Instead of left join why don't you use applymap ..

in your scenario you can write something like this

mapping load

list:

Name,Sex from xyz;

table:

load

Name

applymap('list',Name) as sex,

Category,

Date,

Amount

from aksdj;

HTH

Sushil

[agomes1971]

Hi,

test with this code

List:

LOAD * INLINE [

    Name, Category, Date, Amount

    AB1, A, 29/08/2016, 10

    AB1, A, 30/08/2016, 11

    AB1, A, 31/08/2016, 12

    AB1, A, 01/09/2016, 13

    AB1, A, 02/09/2016, 14

    AB1, A, 03/09/2016, 15

    AB1, A, 04/09/2016, 16

];

Left Join(List)

LOAD * INLINE [

    Name, Sex

    AB1, F

    Ab2, F

];

HTH

André Gomes

[MindaugasBacius]

I would use Applymap.

Like:

map_gender:
mapping load Name
     ,Sex
From Source;

List:
Load Name
     ,Category
     ,Date
     ,Amount
     ,Applymap('map_gender', Name, ':') as Sex
From Source

Give it a try - simple and efficient.

[sunny_talwar]

Can you try this:

List:

LOAD Name,

          Category,

          Date,

          Amount

FROM ...

Left Join (List)

LOAD Name,

          Sex

FROM ...

or

List:

LOAD Name,

          Category,

          Date,

          Amount

FROM ...

Right Join (List)

LOAD Name,

          Sex

FROM ...

[#6]

thank you