Joining data with each column

[Divyajyothi7]

Hi Team

I have following data-set, I would like to optimize multiple V-lookups. Instead of using multiple joints and unions

I'm looking to have dynamic expected output where it can dynamically match even if new rows and columns are added.

old t_m

old t_sm

old t_qa

t_sm

t_qa

t_sm2

t_qa2

t_sm3

t_qa3

t_sm4

t_qa4

t_sm5

t_qa5

SD0789

SD0034

1

SD0055

1

SD0044

1

SD0044

0.99

SD0445

SD0067

0.5

SD0099

2

AB0099

0.4444

SD0088

0.75

SD0993

SD0078

2

SD0088

3

SD0034

2

SD0022

4

RD9900

0.23

SD0022

3.555

t_km	t_km
SD0022	Pigeon
SD0034	Monkey
SD0044	Monkey
SD0055	Pigeon
SD0067	Monkey
SD0078	Pigeon
SD0088	Monkey
SD0099	Pigeon
SD0789	Monkey
SD0445	Monkey
SD0993	Monkey
AB0099	Monkey
RD9900	Pigeon

Expected Output: 

old t_mitm

t_kitm(Name)

old t_sitm

Old_t_kitm(Name)

old t_qana

t_sitm

t_kitm(Name)1

t_qana

t_sitm2

t_kitm(Name)2

t_qana2

t_sitm3

t_kitm(Name)3

t_qana3

t_sitm4

t_kitm(Name)4

t_qana4

t_sitm5

t_kitm(Name)5

SD0789

Monkey

SD0034

Monkey

1

SD0055

Pigeon

1

SD0044

Monkey

1

SD0044

Monkey

SD0445

Monkey

SD0067

Monkey

0.5

SD0099

Pigeon

2

AB0099

Monkey

0.4444

SD0088

Monkey

SD0993

Monkey

SD0078

Pigeon

2

SD0088

Monkey

3

SD0034

Monkey

2

SD0022

Pigeon

4

RD9900

Pigeon

0.23

SD0022

Pigeon

[binuacs · Accepted solution]

@Divyajyothi7 One way of doing this

[Divyajyothi7]

I just want columns should be matched with dataset 2

and it should be dynamic, 

[Divyajyothi7]

is it possible to dynamically sort column headers as per expected output

because in future new columns might be added 

Instead of manual process

[binuacs · Accepted solution]

@Divyajyothi7 If you look at the column names provided in both the input files and the output file, they are different, for eg: 'old t_m' in the first data set, 't_km' in the second data set, and in the output, it will become 'old t_mitm'. So manual intervention is required, if you come up with a pattern or something in the output file field names, we can make it dynamic, here I am seeing some random names which are very difficult to make dynamic. If you have any suggestion let me know we can work on this, because i dont see any pattern to make it dynamic