Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Create Cluster/group for Change History

lnguyen
8 - Asteroid

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

9 REPLIES 9
DiganP
Alteryx Alumni (Retired)

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

Digan
Alteryx
dhtay
8 - Asteroid
@lnguyen What do you mean by "linking" the IDs?
PeterS
Alteryx
Alteryx

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! boba fett.jpg)

 

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

Peter Stoddard
Premium Support Advisor
Alteryx, Inc.


lnguyen
8 - Asteroid

Hi,

I was out of the office until now... sorry for the delay in response.  Attached is the yxdb file with the sample of the ID to an from and the date each change occurs. Thanks in advance for any insights that you can provide

lnguyen
8 - Asteroid

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

lnguyen
8 - Asteroid

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

PeterS
Alteryx
Alteryx

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

Peter Stoddard
Premium Support Advisor
Alteryx, Inc.


lnguyen
8 - Asteroid

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

PeterS
Alteryx
Alteryx

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

Peter Stoddard
Premium Support Advisor
Alteryx, Inc.


Labels