Rows getting duplicated when conditional columns are added

[rsdhavle]

I am joining two tables on one key field. I have to calculate few conditional columns based on values from one column of a second table.

When I add these conditional columns along with columns present in first table inside straight table rows with null entries get added in the table. How to handle this?

[YoussefBelloum]

Hi,

I think we need more details here. would you be able to share some data and the script ? or to attach some screen shots ?

[rsdhavle]

Table1

Client Unique Records	ClientType	Country	Region
A	X
B	Y
C	z

Table 2

Client	Role	RoleName	EmpName
A	1	SalesHead	ABCD
A	2	RegionHead	XYZ
B	3	SalesManager	UVW

Now by joining Table 2 with 1 on key client I need to form additional columns with names as Sales Head , Region Head which should display employee names from table 2 as their values so resulting table will be like below.

Currently I am doing left join for each role id with table 1 as in doing self join but would like to know if there is better solution to handle this scenario like applymap or anything else..

ClientUniqueRecond	clienttype	SalesHead	RegionHead
A	X	ABCD	XYZ

[rsdhavle]

gwassenaar‌ can you suggest a good solution for this?

[PrashantSangle · Accepted solution]

Try like

Load * from Table1;

Left Join

Load  Client,Role,EmpName as SalesHead from Table2

where RoleName='SalesHead';

Left Join

Load  Client,Role,EmpName as RegionHead from Table2

where RoleName='RegionHead';

Left Join

Load  Client,Role,EmpName as SalesManager from Table2

where RoleName='SalesManager';

Regards

[YoussefBelloum]

What is the calculation rule for the new field "region head" here ?

for Client A, it could be XYZ or ABCD, right ?

[PrashantSangle]

I think it is based on RoleName

Regards,

[YoussefBelloum]

Yes, it is the only logical explanation..

[rsdhavle]

Yes I have implemented it like this way currently. I would like to know if we use some function like ApplyMap and create multiple Mapping loads will that be a better solution performance wise or it will not make much difference?