Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RWilliams92
Contributor II
Contributor II

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

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 KeyRef NumberDate OP
11002620009003923 Sep 2019
11002620009003604 Oct 2019
11002620009003516 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

1 Solution

Accepted Solutions
marcus_malinow
Specialist III
Specialist III

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];

View solution in original post

1 Reply
marcus_malinow
Specialist III
Specialist III

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];

View solution in original post