conditionally copying cells to cells and additionally displaying source afterwards

[dwstada]

Hey everyone, I am very new to Alteryx and am encountering a problem I can't solve yet.

Basically, I have non-unique IDs, non-unique letter codes, and dates. Some date cells are a default nonsense 19000101 or just 00000000.

I would like to conditionally copy some dates into other cells and then show where the data was copied from, as shown in the tables below.

So if an ID has several different codes and only one date other than 19000101 and 00000000, then I want to copy that date into the date cell of the corresponding id + code and add another column indicating where the information was copied from.

And:

If an ID has the same code with several dates, I want to copy the earliest date (other than 19000101 and 00000000) into the date cell of the corresponding id + code and add another column indicating where the information was copied from.

My first thought was the summarize tool after changing 19000101 and 00000000 to NULL and displaying minimum of a date, but I don't know how to add a "data copied from" source column that way and it wouldn't work for IDs with different codes and only one date.

Is there an easy way to do any of this, maybe with the formula tool?

Thank you in advance

input

ID	CODE	DATE
ID1	XX1	date1
ID2	XX1	00000000
ID2	XX2	date2
ID2	XX3	19000101
ID3	XX2	date3
ID4	XX2	date4
ID4	XX3	date5
ID5	XX1	date6
ID5	XX1	date7 (later than date6)
ID5	XX4	00000000

output

ID	CODE	DATE	SOURCE
ID1	XX1	date1
ID2	XX1	date2	"date from ID2 XX2"
ID2	XX2	date2
ID2	XX3	date2	"date from ID2 XX2"
ID3	XX2	date3
ID4	XX2	date4
ID4	XX3	date5
ID5	XX1	date6
ID5	XX1	date6	"min date from ID5 XX1"
ID5	XX4	date6	"date from ID5 XX1"

[bkurt]

You can use the find replace function for this. 

Filter out the rows with 00000000 and 19000101

then connect the two files with find replace based on ID to change the date.

[Emmanuel_G · Accepted solution]

@dwstada 

Find in attachement the way of doing that.

I did a test with dummy values of real dates.

Please let me know if this solution is ok.

[dwstada]

@Emmanuel_G thank you, this is great