Edwin Rutten 2 Posted February 10, 2020 Share Posted February 10, 2020 Hello, I want to link the Groupname to a Custnumber and his revenue, Catch due to historical changes the Groupnames can change over time how can i do this, based on the link by my cust names reflects the incorrect revenue numbers per Group. What must i do Group_ID Group_Name START END Cust_ID Cust_Name 8000091 AAAA 200901 201912 1193875 Kkkkkk 8000102 BBBB 202001 205012 1193875 Kkkkkk 8000091 AAAA 201701 205012 1191272 Pppppp 8000897 CCCC 201104 201612 1191272 Pppppp 8000102 BBBB 201501 201808 1176547 Qqqqqq 8000286 DDDD 201809 205012 1176547 Qqqqqq 8000102 BBBB 201310 205012 1191108 Rrrrrr Cust_ID Cust_Name Period Revenue 1193875 Kkkkkk 200902 50.171.384 1193875 Kkkkkk 202003 183.560.924 1191272 Pppppp 201702 124.391.862 1191272 Pppppp 201106 41.463.954 1191108 Rrrrrr 201311 829.279 1176547 Qqqqqq 201502 19.607.843 1176547 Qqqqqq 201810 40.542.533 1193875 Kkkkkk 200903 40.542.533 1193875 Kkkkkk 202004 287.483.414 1191272 Pppppp 201705 160.198.290 1191272 Pppppp 201106 165.855.816 1191108 Rrrrrr 201312 40.542.533 1176547 Qqqqqq 201501 202.712.664 1176547 Qqqqqq 201810 20.271.266 Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted February 11, 2020 Share Posted February 11, 2020 One quick solution would be as below. Note this solution uses 10.x version: 1) Insert columns period and revenue matching on cust_id from both tables with left outer join 2) Add transformation to calculate new column which identifies whether period value is between start and end date. Filter out rows whose period value is not between start and end dates 2. Select Data > Add... Source: Data table from current analysis Data table: Data Table (3) Update behavior: Automatic Data loaded at: 2/11/2020 5:27 PM Data was added as new columns in data table 'Data Table (2)' Matching behavior: Tries to match the specified columns when data is loaded Matched columns: Cust_ID Cust_ID Added columns: Period Revenue Ignored columns: Cust_Name Join method: Left outer join Treat empty values as equal: No 3. Select Data > Transform data... Added transformations Transformation name: Calculate new column Column name: IncludeRows Expression: case when ([Period]>=[sTART]) and ([Period] Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now