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 :
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
Solved! Go to Solution.
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...
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
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.
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.
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