Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Need to perform mapping for Profit and Loss Data

rajvivan11
8 - Asteroid

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

Actual Data.PNG

Expected Output

Expected Output.PNG

 

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

5 REPLIES 5
fharper
12 - Quasar

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...

  1. 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.
  2. 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
8 - Asteroid

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
Alteryx
Alteryx

@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.

rajvivan11
8 - Asteroid

ALteryx Solution.PNGThanks 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 

 
rajvivan11
8 - Asteroid
Hi Jessie,
Can we achieve the same output with less number of joins? We have 40 million records of data and it is taking almost 1.5 hours to run the entire Process .Any thoughts ?
Labels