topic Single table join on different column names in QlikView

Single table join on different column names

Anonymous — Thu, 24 Oct 2013 19:29:00 GMT

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

Re: Single table join on different column names

Nicole-Smith — Thu, 24 Oct 2013 19:51:23 GMT

User:

LOAD

UserNo,

UserName,

CreatedbyUserNo

FROM YourDataSource;

LEFT JOIN (User)

LOAD

UserNo,

UserName,

CreatedbyName

FROM YourDataSource2;

Re: Single table join on different column names

Anonymous — Thu, 24 Oct 2013 19:59:47 GMT

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.

Re: Single table join on different column names

MayilVahanan — Thu, 24 Oct 2013 20:01:49 GMT

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;

Re: Single table join on different column names

Nicole-Smith — Thu, 24 Oct 2013 20:03:34 GMT

User:

LOAD

UserNo,

UserName,

CreatedbyUserNo

FROM YourDataSource;

LEFT JOIN (User)

LOAD

UserNo as CreatedbyUserNo,

UserName as CreatedbyName

RESIDENT User;

Re: Re: Single table join on different column names

Anonymous — Thu, 24 Oct 2013 20:06:10 GMT

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

Re: Single table join on different column names

Thu, 24 Oct 2013 20:37:36 GMT

Hi Jerry, Try with this code.

Result:

Regards