Single table join on different column names

[Anonymous]

I have a single table with the following fields:

UserNo	UserName	CreatedbyUserNo
1	John Smith	2
2	William Jones
3	Sally Adams	2
4	George Doe	3
5	Lynda Peters	3

I would like to look up the name of who created each of the users.  What would the load script statements look like?

UserNo	UserName	CreatedbyName
1	John Smith	William Jones
2	William Jones
3	Sally Adams	William Jones
4	George Doe	Sally Adams
5	Lynda Peters	Sally Adams

[Nicole-Smith]

User:

LOAD

UserNo,

UserName,

CreatedbyUserNo

FROM YourDataSource;

LEFT JOIN (User)

LOAD

UserNo,

UserName,

CreatedbyName

FROM YourDataSource2;

[Anonymous]

The load should be from one single data source.  The illustration shows the input table as table 1 and the desired output as table 2.

[MayilVahanan]

HI

Try like this

Table1:

LOAD

UserNo & '_'& UserName As Key,

UserNo,

UserName,

CreatedbyUserNo

FROM table1;

Edit:

Map1:

Mapping

Load UserNo, UserName AS CreatedbyUserNo

Resident Table1;

Load

     Key, UserNo, UserName, ApplyMap('Map1', CreatedbyUserNo) As CreatedbyName

Resident Table1;

Drop table Table1;

[Nicole-Smith]

User:

LOAD

UserNo,

UserName,

CreatedbyUserNo

FROM YourDataSource;

LEFT JOIN (User)

LOAD

UserNo as CreatedbyUserNo,

UserName as CreatedbyName

RESIDENT User;

[Anonymous]

Hi

I am a fan of applymap() that Mayil Vahanan Ramasamy mentioned,  so you could try this:

MyData:

LOAD * INLINE [

    UsewrNo, UserName, CreatebyUserNo

    1, John Smith, 2

    2, William Jones

    3, Sally Adams, 2

    4, George Doe, 3

    5, Lynda Peters, 3

];

MyMap:

Mapping load

CreatebyUserNo ,

UserName

resident MyData

;

RevisedTable:

load

UsewrNo ,

UserName ,

applymap ( 'MyMap' , CreatebyUserNo ) as CreatedByName

resident MyData

drop table MyData ;

Best Regards,     Bill

[#7]

Hi Jerry,  Try with this code.

Result:

Regards