Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
User:
LOAD
UserNo,
UserName,
CreatedbyUserNo
FROM YourDataSource;
LEFT JOIN (User)
LOAD
UserNo,
UserName,
CreatedbyName
FROM YourDataSource2;
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.
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;
User:
LOAD
UserNo,
UserName,
CreatedbyUserNo
FROM YourDataSource;
LEFT JOIN (User)
LOAD
UserNo as CreatedbyUserNo,
UserName as CreatedbyName
RESIDENT User;
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
Hi Jerry, Try with this code.
Result:
Regards