This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 :
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 .
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.
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.
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.