Mapping with column names in qlik sense

[govardhana_r]

I have 2 tables as attached.

First table is an hierarchy level. Second table is a mapping table.

Table1 

Level 1	Level 2	Level 3	Level 4	Level 5	Level 6
DeptABC	DeptABCTech	TechJava	JavaExpert	JavaSpring	JavaCore
DeptABC	DeptABCTech	TechQlik	QlikExpert	QlikSense	QlikView
DeptABC	DeptABCMech	Mechanic	BikeExpert	Exports	Fixing
DeptDEF	DeptDEFCivil	Construction	Factories	Expert	Engineer

Table 2

Mapper	Level1	LevelValue	Level2	Level2Value
IT Industry	2	DeptABCTech	5	JavaSpring
Mechanical	5	Exports
Civil	3	Construction
IT Industry Qlik	2	DeptABCTech	6	QlikView

We have to find the Mapper value for the first table. For Eg 

The Level1 column in the second table refers to the Column Level number in the first table

lets take the first row, Level 1 is DeptABC, but there is no 1 in Level1 in second table, so now we look for Level 2 which is DeptABCTech. There are 2 row matching. So we move on the Level 3, then Level 4 which is not found. Now we move on to Level 5 which was found and it is JavaSpring. So the final result will be the Mapper columns "IT Industry".

If a match is not found we just say "Not Found".

Below is the desired output

Level 1	Level 2	Level 3	Level 4	Level 5	Level 6		Required Result
DeptABC	DeptABCTech	TechJava	JavaExpert	JavaSpring	JavaCore		IT Industry
DeptABC	DeptABCTech	TechQlik	QlikExpert	QlikSense	QlikView		IT Industry Qlik
DeptABC	DeptABCMech	Mechanic	BikeExpert	Exports	Fixing		Mechanical
DeptDEF	DeptDEFCivil	Construction	Factories	Expert	Engineer		Civil

[vinieme12 · Accepted solution]

As below

temp:
crosstable (Lvl,Val,1)
load * inline [
Mapper,Level1,LevelValue,Level2,Level2Value
IT Industry,2,DeptABCTech,5,JavaSpring
Mechanical,5,Exports, , 
Civil,3,Construction, , 
IT Industry Qlik,2,DeptABCTech,6,QlikView
];

Map_values:
Mapping Load
Val,Mapper 
Resident temp;
drop Table temp;

Load *
,recno() as id
,ApplyMap('Map_values',Level1,ApplyMap('Map_values',Level2,ApplyMap('Map_values',Level3,ApplyMap('Map_values',Level4,ApplyMap('Map_values',Level5,ApplyMap('Map_values',Level6,'Not Found')))))) as new_Value
inline [
Level1,Level2,Level3,Level4,Level5,Level6
DeptABC,DeptABCTech,TechJava,JavaExpert,JavaSpring,JavaCore
DeptABC,DeptABCTech,TechQlik,QlikExpert,QlikSense,QlikView
DeptABC,DeptABCMech,Mechanic,BikeExpert,Exports,Fixing
DeptDEF,DeptDEFCivil,Construction,Factories,Expert,Engineer
];

[govardhana_r]

Works great. Thanks.