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" |
