Official Id changed for some Records

[MZ900605]

OLD data our records:Old saved data source#1
New data provided by another source:New data provided by another source#2

Hello guys am new to alteryx so I need your help, in our records official ID will change every year in my case the data we have is still on last year official id, and some names are misspelled in our data(records), how should i update  OFFID in the first records. RED rectangle (old data) Table 1.... GREEN rectangle is the new OFFID we should update.

[OllieClarke]

Hi @MZ900605 it would really help us in the community if you could provide some sample data, both inputs and desired output, as well as any logic that would be required to get to your output. It also looks like your post got published before you finished writing it 🙂

Ollie

[MZ900605]

is this good enough?

[OllieClarke]

Hey @MZ900605 

Assuming that the ID column allows us to know sort the data correctly, then we can use a sample tool grouped by [NAME] to take the last row per name (see screenshot below). If the latest data will always have a higher ID than previous data then this will be the data we want to keep. 

In my example there was a name in the old data source which didn't appear in the new one, so this data will be kept at the end. If you just want to use the latest data source rather than combine and keep like below, then you could maybe use a directory tool and the filename to work out which file to use.

Hope that helps,

Ollie

Sample Tool Setup

[MZ900605]

hello again, Sorry I think I misexplained my problem... The old data in our system has an old OFFID which is updated by the Master company.
Data in our System(OLD) Source#1

The Master data has their own big data way more than we need and I cannot add them all to our system.. what I need is to update the new OFFID into the old source and also if I can fix some mistakes in the names (actual names not 3 letters).

And the first column is different it doesn't help in sorting.

Master Data updated Official source#2

So, can I update OFFID.

[OllieClarke · Accepted solution]

@MZ900605 in this case you'll probably need to join the data sets together. However, if there are mistakes in the name, and the OFFID's are wrong then it could be tricky to find a join clause. In the actual data sets, is there a good way to link rows from between them? (I also would be cautious of using Address, as multiple people could live in 1 address, and also they tend to come from free-text fields so it's unlikely to be a good join clause).