Joining 3 data tables up - finding only values attributed to Max Date

RWilliams92 — Mon, 05 Aug 2019 13:46:51 GMT

I've got 2 tables joined up no problem but joining the 3rd is where my issue is.

For context the join key isn't the problem, it's the data accompanying it that I'm trying to join (using left or right joins) correctly. I want to only have one value from both ref number and date assigned to each unique join key.

Each join key can have duplicates (see below) however the ref number and date will be unique. I'm wanting to pull through the ref number assigned to the Max Date (from Date OP column) - i.e. the data in bold below, and ignore the other data (so ignoring the other 2 rows below).

Join Key	Ref Number	Date OP
11002	6200090039	23 Sep 2019
11002	6200090036	04 Oct 2019
11002	6200090035	16 Jul 2019

I've tried

Right Join (X_INITIAL)
LOAD [Join Key],
Max("Date OP") as Max_OP
RESIDENT X_INITIAL
Group By [Join Key];

But this doesn't pull through what i'm wanting.

Any help please?

Thanks

Re: Joining 3 data tables up - finding only values attributed to Max Date

marcus_malinow — Mon, 05 Aug 2019 14:05:20 GMT

I'm assuming that having loaded your table you're only wanting to keep the record with the max Date OP.

In which case the below code should work.

Right Join (X_INITIAL)
LOAD [Join Key],
Max("Date OP") as [Date OP]
RESIDENT X_INITIAL
Group By [Join Key];

topic Joining 3 data tables up - finding only values attributed to Max Date in App Development

Joining 3 data tables up - finding only values attributed to Max Date

Re: Joining 3 data tables up - finding only values attributed to Max Date