Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Single table join on different column names

I have a single table with the following fields:

UserNoUserNameCreatedbyUserNo
1

John Smith

2
2William Jones
3Sally Adams2
4George Doe3
5Lynda Peters3

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

UserNoUserNameCreatedbyName
1

John Smith

William Jones
2William Jones
3Sally AdamsWilliam Jones
4George DoeSally Adams
5Lynda PetersSally Adams
6 Replies
Highlighted

Re: Single table join on different column names

User:

LOAD

UserNo,

UserName,

CreatedbyUserNo

FROM YourDataSource;

LEFT JOIN (User)

LOAD

UserNo,

UserName,

CreatedbyName

FROM YourDataSource2;

Highlighted
Contributor III
Contributor III

Re: Single table join on different column names

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.

Highlighted

Re: Single table join on different column names

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;

Highlighted

Re: Single table join on different column names

User:

LOAD

UserNo,

UserName,

CreatedbyUserNo

FROM YourDataSource;

LEFT JOIN (User)

LOAD

UserNo as CreatedbyUserNo,

UserName as CreatedbyName

RESIDENT User;

Highlighted

Re: Re: Single table join on different column names

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

Highlighted
Not applicable

Re: Single table join on different column names

Hi Jerry,  Try with this code.

Script.PNG.png

Result:

Table.PNG.png

Regards