Data table - combine and keep most recent

[adrian_sullivan]

hi all,

i have an issue where i need to create datatables and then combine them, but then keep only the most recent entry. what i am working on has many more fields than this but shows the probelm

over 3 periods there are 3 sets of sales figures - dt_1, dt_2 and dt_3. what i am trying to get to is dt_final

if i use merge i will get two lines for ID 2 Mary, and 3 lines for ID 3 Joe, removing dplicates does not help as the records are unique.

any solution where i could keep only the most recent ID would be great

thanks
adrian
DT_1.txt (47 Bytes)
DT_2.txt (48 Bytes)
DT_3.txt (36 Bytes)

[Anil_G (Anil Gorthi)]

@adrian_sullivan

on what basis you define the latest one? is there a date column?

if so we can first merge and then get the latest using date

cheers

[adrian_sullivan]

good question

they would be individual days
so dt_1 would be Monday

dt_2 would be Wednesday
but should also include data from dt_1

dt_3 would be Friday
DT_final would be data from dt_1,2,3

currently there is no date column, but i do have the option of adding one

[Anil_G (Anil Gorthi)]

@adrian_sullivan

if you add date that is easy

and as per screenshot dt2 or dt1 does not have remaining dt data…so I dont know how you are saying they are there

but if they are not present and if you know dt1 is first and dt3 is last…just add a new column and give default value as 1,2,3…and instead of date we can use that also by checking the highest nu ber and getting it…for example if an item is there in 1,2,3 then 3 is selected…if present only in1 then 1 is selected…that also can be done

cheers

[adrian_sullivan]

i will add a date and then try and get ti pick up on the maxdate, may need to come back to you again, but thamks for the help so far

[adrian_sullivan]

so instead of adding a date i added a counter that will increment everytime the job runs. i also changed ID to employee ID.

Still not certain how to get the max run id per employee id though

updated txtx \ csv files attached
Updated DT_1.txt (71 Bytes)
Updated DT_2.txt (70 Bytes)
Updated DT_3.txt (58 Bytes)

[adrian_sullivan]

so after a bit of research and some chat-gpt trial and error i got this

create an assign activity for a new dt
DT_Result = (From row In DT_Final.AsEnumerable()
Group row By empID = Convert.ToInt32(row(“Employee ID”)) Into grp = Group
Let maxRunID = grp.Max(Function(r) Convert.ToInt32(r(“Run ID”)))
Select grp.Where(Function(r) Convert.ToInt32(r(“Run ID”)) = maxRunID).First()).CopyToDataTable()

that worked

so @Anil_G thanks for the advice about the date \ counter