Cant get the correct value in a column

[Jimvirt]

Hi Good Day I have this problem in my script that i cant solve I have this 2 columns that has this value( Please see Table Below). The business wants the Leadership Career Level Only has the number or the Level and the Host Classification has the Career.

Host Classification	Leadership Career Level
6
Senior Manager	5
Senior Manager	5
Senior Manager	5
	Managing Director
7

I tried mapping on this issue but I can only fix the Leadership Career Level (please see Desc Below). Now the issue is when a the level is in the host classification before both Host and Leadership is Null or blank.

Host Classification	Leadership Career Level
-	-
Senior Manager	5
Senior Manager	5
Senior Manager	5
Managing Director	7
-	-

This is what my code is looking like (Not All of it):

HostClassification1:
Mapping Load * Inline
[
LeadershipCareerLevel, HostClassification
3, Senior Managing Director
4, Sr. Systems Analyst
5, Senior Manager
6, Managing Director
7, Manager
8, Consultant
9, Consultant
10, Analyst
11, Analyst
12, Associate
13, Associate
Senior Manager, 5
Accenture Leadership, 6
Manager, 7
Consultant, 8
Consultant, 9
Analyst, 10
Analyst, 11
Associate, 12
Associate, 13

];

LeadershipCareerLevel:
Mapping Load * Inline
[
HostClassification, LeadershipCareerLevel
'Senior Manager', 5
'Senior Manager', 6
'Accenture Leadership', 6
'Managing Director', 6
'Manager',7
'Consultant',8
'Consultant',9
'Analyst',10
'Analyst',11
'Associate',12
'Associate',13

]

;

ApplyMap ('HostClassification1',LeadershipCareerLevel) as [Host Classification] ,

ApplyMap ('LeadershipCareerLevel',HostClassification) as [Leadership Career Level]

--End


If you guys could help me on this that would be great thanks!

[Stoyan_Terziev]

Hi Jim,

If I understand correctly, the issue is the mix between the values in the columns you have (Host Classification and Leadership Career Level)

In that case, an approach like this may help you fix it:

HostClassification1:
Mapping Load Distinct
If(IsNum(LeadershipCareerLevel), LeadershipCareerLevel,HostClassification) as LeadershipCareerLevel
, If(IsText(HostClassification), HostClassification,LeadershipCareerLevel) as HostClassification
;
LOAD * Inline
[
LeadershipCareerLevel, HostClassification
3, Senior Managing Director
4, Sr. Systems Analyst
5, Senior Manager
6, Managing Director
7, Manager
8, Consultant
9, Consultant
10, Analyst
11, Analyst
12, Associate
13, Associate
Senior Manager, 5
Accenture Leadership, 6
Manager, 7
Consultant, 8
Consultant, 9
Analyst, 10
Analyst, 11
Associate, 12
Associate, 13
];

Also, I'd remove the ' signs if you are doing inline load in the second mapping table. Not that it will break it, but not best practice to have those extra symbols which get indirectly reduced at the back-end.

I hope that helps!

Kind regards,

S.T.

[Brett_Bleess]

Jim, did Stoyan's post work for you?  If so, please be sure to use the Accept as Solution button on his post to give him credit for the help and to let others know that worked for your use case.  If you are still working on things, please leave an update.

Regards,
Brett