Hello,
My objective is to join two queries but only take the last match for the query on the right. I'll try to explain.
I have two queries that look like this:
Query 1
ID | Open State | Open Date
1 | New | 20 Jun 2014
2 | New | 20 Jun 2014
3 | New | 22 Jun 2014
4 | New | 25 Jun 2014
Query 2
ID | Close State | Close Date
1 | Closed | 22 Jun 2014
2 | Closed | 21 Jun 2014
2 | Closed | 23 Jun 2014
3 | Closed | 22 Jun 2014
In this particular case, object with ID "2" has been Closed twice. My objective is to have a join that will only join the last instance for a particular object. I would like to see:
Joined Query (outer join)
ID | Open State | Open Date | Close State | Close Date
1 | New | 20 Jun 2014 | Closed | 22 Jun 2014
2 | New | 20 Jun 2014 | Closed | 23 Jun 2014
3 | New | 22 Jun 2014 | Closed | 22 Jun 2014
4 | New | 25 Jun 2014 | |
Instead, I am seeing both matches for the object like this:
ID | Open State | Open Date | Close State | Close Date
1 | New | 20 Jun 2014 | Closed | 22 Jun 2014
2 | New | 20 Jun 2014 | Closed | 21 Jun 2014
2 | New | 20 Jun 2014 | Closed | 23 Jun 2014
3 | New | 22 Jun 2014 | Closed | 22 Jun 2014
4 | New | 25 Jun 2014 | |
Is this possible? Any feedback would be greatly appreciated!
Regards,
Andrew
In your Query 2, can't you just use maximum([Close Date]) instead of [Close Date]?
I think that would give you the results you're after...
C
Hello Chris,
I tried using "maximum([Close Date])" as per your suggestion but it still gives multiple results. Does this function work on date fields?
Regards,
Andrew
EDIT: I have also tried using "maximum([Close Date] for [ID])" but that doesn't work either. To elaborate, I am trying these under the Data Items section of the query.
EDIT2: I did it! I put "[Close Date] = maximum([Close Date] for [ID])" as a filter in the query and it works!