Create Cluster/group for Change History

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?

Transformation

Batch Macro

Data Investigation

Accepted answers

PeterS

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

Make Groups Example.yxmd

PeterS

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

All comments

DiganP

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

dhtay

@lnguyen What do you mean by "linking" the IDs?

PeterS

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 )

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

Make Groups Example.yxmd

lnguyen

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

sampleData.yxdb

lnguyen

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

lnguyen

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!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335