dynamic date associating

NathanDorsch

I have a dataset that contains IDs and meta data to include modification dates. Each ID has a last modification date. However, each ID may (or may not) be associated with other IDs. I want to identify the latest modification date (direct or associated) for each ID.

Below is an illustration of what it looks like, except there are a lot more columns and some of the IDs are associated with upwards of hundreds of other IDs… and there will be a growing list of new IDs and associations with other IDs.

Does anyone have any suggestions for how to do this in ETL?

ID	name	last modification date	associated IDs	Latest date associated	Notes
1	asdf	3/4/2023	5, 6	3/4/2023	1 is the oldest date - no change
2	te	6/7/2022
3	sdg	4/5/2019	4, 5, 6	5/5/2023	4 is the oldest date
4	fewq	5/5/2023
5	we	2/3/2020	1, 2	3/4/2023	1 is the oldest date
6	tgrw	12/11/2022

marcel_luthi

To provide more clarity on my answer, your flow will end up looking something like:

Which after running provides the following output:

The modifications to the original one is on the number of days we take from the calendar, so we no longer limit it to a calendar year but to a set number of days, this happens in the filter of the Domo Calendar section (here I used 10 days as I didn't expect anything to have a list of associated IDs with more than 10 entries, but you can change the number to whatever you want):

The other key change is that I'm self adding a reference to itself on the (Self Associating SA) as part of the formulas to your original dataset, and that we measure on the number of commas, not spaces:

When we split we also make sure to remove whitespaces and transform to an integer, so that we can do the join afterwards:

The select Columns 1 narrows it down only to the ID and date, which is what we care for, and then on the group by we select all the original columns from your dataset, plus perform a MAX aggregation on the joined one.

This is scalable in the way that you can accept lists of up to N associations, but how fast it'll be to run on your real data with thousands or more entries is something you'd need to test.

ColemenWilson

You can use a rank and window tile in Magic ETL to rank the rows partitioned by the ID. Then you can use a filter tile to only keep those ranked 1 (most recent).

NathanDorsch

Thanks @colemenwilson, but I'm not sure I follow. I can rank the IDs by their last modification date (see new columns: "Date Rank"), but I'm still left with the same issue. Can you elaborate more on how I can use the date rank column?

ID	name	last modification date	Date Rank	associated IDs	Latest date associated	Notes
1	asdf	3/4/2023	2	5, 6	3/4/2023	1 is the oldest date - no change
2	te	6/7/2022	4
3	sdg	4/5/2019	6	4, 5, 6	5/5/2023	4 is the oldest date
4	fewq	5/5/2023	1
5	we	2/3/2020	5	1, 2	3/4/2023	1 is the oldest date
6	tgrw	12/11/2022	3

ColemenWilson

Sure thing! You are on the right track, you just need to partition by ID, as shown below:

You'll be left with a ranking by date for each ID. So where there are duplicate ID's, lets say 3 duplicates, you'll have ranks 1,2,3 for the corresponding IDs. Then you can use a filter tile to remove anything that isn't ranked 1 - leaving you with the rows with the most recent modification date for each ID.

NathanDorsch

@colemenwilson I'm not sure I've properly explained what I'm attempting to do here…

Each row is a unique ID - so there aren't any duplicate IDs. What I am attempting to do is create an overall last modification date for each ID (based on it's last mod date and the last mod dates of the IDs it is associated with).

So for example, looking at ID 3 in my illustration. The last mod date for 3 is 4/5/19. However, ID 3 is also associated with IDs 4, 5, & 6. The latest modification date among those three is 5/5/23 (which belongs to ID 4). So the overall last mod date (based on direct/association) for ID 3 is 5/5/23.

I'm unsure how to do this, as it will involve some type of dynamic lookup.

ColemenWilson

Ahhhhhh okay. So what you'll do is split out the associated IDs column using the split column tile in Magic ETL. Then join the last modified dates to each of the new associated id's columns. Then use the GREATEST() function to find the most recent date across the columns for each row:

GREATEST(`Associated ID Date 1`, Associated ID Date 2`, `Associated ID Date 3`, `Associated ID Date 4`, `Associated ID Date 5`)

NathanDorsch

@colemenwilson two issues: 1) some of the IDs will be associated with upwards of several hundred IDs.

2) there will be new IDs added and new ID associations (with other IDs).

I'm familiar with the split column function, but would need a dynamic way of splitting up the columns and then associating them.

marcel_luthi

I would need to test this out, but ideally, you could solve this with a variation on @MarkSnodgrass word cloud example (YouTube link), and after exploding to have one row per each associated ID, plus one row for the unassociated version, where you'll Join the data from the associated IDs (only the field you care for), then use a GroupBy tile to get the MAX of the dates for them. This should theoretically work … whether it'll be fast or efficient since you mentioned IDs will be associated with upwards of several hundred IDs that's a different story. Also this approach will only work assuming you only care about direct associations as you mentioned in a comment before.

marcel_luthi

To provide more clarity on my answer, your flow will end up looking something like:

Which after running provides the following output:

The modifications to the original one is on the number of days we take from the calendar, so we no longer limit it to a calendar year but to a set number of days, this happens in the filter of the Domo Calendar section (here I used 10 days as I didn't expect anything to have a list of associated IDs with more than 10 entries, but you can change the number to whatever you want):

The other key change is that I'm self adding a reference to itself on the (Self Associating SA) as part of the formulas to your original dataset, and that we measure on the number of commas, not spaces:

When we split we also make sure to remove whitespaces and transform to an integer, so that we can do the join afterwards:

The select Columns 1 narrows it down only to the ID and date, which is what we care for, and then on the group by we select all the original columns from your dataset, plus perform a MAX aggregation on the joined one.

This is scalable in the way that you can accept lists of up to N associations, but how fast it'll be to run on your real data with thousands or more entries is something you'd need to test.

NathanDorsch

thanks @marcel_luthi I'll take a look at this.