Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
];
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
];
Works great. Thanks.