Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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];