Need to perform mapping for Profit and Loss Data

Good Morning ALteryx ACE/Alteryx Lovers

Business Requirement :

I have a Profit and loss data which is coming from Oracle Database. In that Data , we have to perform management level mapping .In other words, how senior management manages their PnL for their respective Business Units.The Challenge is oracle data doesn't have mapping and we need to ingest the mapping from outside via a mapping table.

I am facing challenge in mapping my final data with other mapping Table. Can some one please help me getting the Desired output:

Mapping Table :

mapping table.png

Actual data

Expected Output

For example :

In our actual Data , we have

ID 1= $C and RC Level 1 = Corporate - So L1 Member - SAM

Note 😘L1 member will always be SAM irrespective of ID/Level. This is highest level of management

ID 2= $CA and RC Level 2 = Corporate Audit - So L2 Member - ALICE

ID 3= $CAA and RC Level 3 = Corporate Advisory - L3 Member - JACKY

Point to be Noted :
If you notice Row 4 of Actual Data , we don't have any value in ID 3 and RC_Level_3, but we do have value in ID4 and RC_LEVEL_4

ID 1= $C and RC Level 1 = Corporate - So L1 Member - SAM
ID 2= $M and RC Level 2 = Managing Fee - So L2 Member - ALICE
ID 3= NULL and RC Level 2 = NULL -
So For L3 Member - It will look for the next mapping and put it into L3 Member
ID 4= $SA and RC Level 4 = Service Fees - So L3 Member - JOHN

In my actual data ,we have 40 members and 70 ID's so We cannot hardcode data by using IF Else condition, we are looking for a dynamic approach which gives us the desired output. In addition to that , People do move from BU to another BU or may move out of organization. We are getting this mapping table in every start of month . Hope this make sense .

Thanks in advance,

Regards,

Rajeev

Alteryx Issue.xlsx

Common Use Cases

Accepted answers

rajvivan11

ALteryx Solution.PNG Thanks Jessie.. I truly appreciate . Right now I am going through the workflow and trying to understand how you achieved it. Hope I will be able to crack

All comments

fharper

Thank you @rajvivan11 this is much clearer. only one thing. can there be more than 4 groups of ID and Level in the actual data? you specify only there levels of people in the mapping...but 4 groups of ID and Level...

From your explanation it seems that for the first group it will always be L1 member, nd ID_2 it will always be L2 Member and L3 member will be used for ID_3, if not null, and if ID_3 is null then ID_4 if not null.

Which brings questions of what if ID_2 is null and ID_3 is populated? this goes back to the questions previously of what are the scenarios. What are the permutations possible in the data.

Also I still see no relationship of the column Level in mapping to the data....is it meaningful in some way.

This said there are almost always more than one way to solve...

One way is to join the map to data (bringing in the data on the left connector for discussion purposes) on ID_1 and RC_Level_1 to ID & Description of Mapping and passing the L1 Member value, clean up columns in the join config, then union left and join connectors and pass into another Join, basically same as before but joining with ID_2 and RC_Level_2 of the data this time....and so on. This join/union set is repeated for the 4 iterations of ID and RC_Level. For the last iteration allow the L3_Member column to be renamed to Right_L3_Member then add a formula to modify L3_Member.... if isempty([L3_Member]) then [Right_L3_Member] else [L3_Member] endif then drop unwanted columns in a subsequent select.
You could transpose Mapping to have one row per ID, Description, Level and person so L1_Member is on one row and L2_Member is on another row etc. then do similar to data. then join then clean up and cross tab back into a single row format.

The first option is easier for most, if the number of ID & Level iterations in the data were unknown then the second option might be better.

Sorry I don't have Alteryx available to provide a working flow example but these descriptions should provide you enough to build your own based on what you have already.

Good luck

rajvivan11

Let me answer your question :

1. can there be more than 4 groups of ID and Level in the actual data >? - Yes we have mapping till Level 7

2. what if ID_2 is null and ID_3 is populated -> It will look for the upcoming value and assign it to L2 Member . if there is null value, it should move horizontally and look for the next value and assign it respective members

3.Is there no relationship of the column Level in mapping to the data - > Yes you are right. There is no relationship between column level and mapping.We are only checking IDs and their respective Members. If L1 is filled then we will See where the next ID is and what it contains in L2 and L3 Mapping and based on that assign it to expected output.

I read your description and tried but no luck. This seems too complicated for my tiny little brain, so It would be great if you can attach the workflow whenever you get time.

JessieC

@rajvivan11 ,

I mocked something up, with manual joins/unions (bottom container) as well as using transposing (more dynamic), however the main issue is the null values needing to shift over for the next value. I was able to use a Multi-Row Formula tool to shift over, but I'm not sure how it'll work with your actual data set with 40 members and 70 IDs.

Attached should get you started at least.

Mapping.yxmd

rajvivan11

ALteryx Solution.PNG Thanks Jessie.. I truly appreciate . Right now I am going through the workflow and trying to understand how you achieved it. Hope I will be able to crack

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703