Hi,
I have an interesting issue relating to the historical data on our system. It records sales by UserID. The UserID changes from time to time for whatever reason and this change is recorded on a different table where Old ID, New ID, and Date are available. The sales record will retain the ID on the date it was recorded. This created a challenge in matching the current User ID with historical sales Data.
Essentially, one person can have a number of IDs throughout time; most don't. The challenge is to group/link all of these changed IDs together in order to link the old IDs with the latest.
Does anyone know a way to link these changes in chronological order?
LT
Solved! Go to Solution.
@lnguyen Can you provide a sample dataset with before/after result? This would help understand the issue. I would imagine using a join tool with a sort tool to get the appropriate dataset.
Hi @lnguyen
I think your question about associating related usernames could be a great use case for the Make Group tool!
(Also see another example in Alteryx Designer under Help - Sample Workflows - Learn one tool at a time - Join - Make Group, especially if you like Star Wars! )
Using your table of username changes with Old ID and New ID the Make Groups tool will associate all records that are connected and give them a grouping. Then through some data manipulation you can find the most recent username and associate it back to your sales data.
I've attached an example (made in Alteryx Designer version 2018.4) that I put together based off what I perceived your data to be like but should give you an idea for a starting point. Hope it helps!
Peter
PeterS,
Thanks. That did the trick.
I add the tile tool after the make group tool to add the unique ID to each group.
it works great
Thx again
LT
so, what I was thinking is (from the sample data) to link the original ID with the destination ID to find a match. The problem is that it only identify the next ID change from the second id change. I need to find the fourth and fifth ect. ID change until there is no change. Somewhere along the line, I got lost!
I got to about 263 matches before I got lost. Using the tool PeterS suggested, I found 789 matches. If I only joined the force earlier!
LT
Hi @lnguyen glad that tool helped! I don't get to use the Make Group tool too often, your question was a good opportunity!
Have a great weekend!
Peter
PeterS,
I have another interesting business problem that is group related which, in light of this tool you have suggested, I want to explore. The scenario is below
There is a list of User ID and each of this User can be part of a team or two. So in the second column of this list, the team ID would be provided.
from time to time, current user can leave the team or new users can be added to the team (or two). This type of activity is recorded as a flag (yes or no) to either "Add" or "Delete". Along with this is the date when the event happened.
Added to the complexity of this information is the way how the admin to this data is going to record the information. The preferred way is to just record whom got added and whom got deleted on this date. However, sometimes the information is entered, removed and re-entered for the same user repeatedly. For example, on date 1 user 1 is entered as added but some other information needs to be corrected so this entry is reversed and re-entered again. Therefore this user ID would show up 3 times on this date, one as add, the other as delete and again as add. This pattern is not very consistence depending on the additional information needs to be corrected so this user can be 'added' three time on this date.
What I want to get in the end is the list of group and its active members on the date of change.
What kind of logic would you suggest using this tool?
Best,
Thanks,
LT
Hi @lnguyen
For this questions I think it might be best to start a new discussion so that it can have more visibility in the discussion board as a new post. This way more Community members will see it and If you are able to attach some example data to the new post then other users can help come up with some ideas more easily. Be sure to post a link to the new post here so users can follow it from this post.
Thanks!
Peter