We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
fireworks
SOLVED

Select Max value given two criteria

jdejesus
7 - Meteor

Hello,

 

I am looking to select a row based on the max number of two criteria. In the example below, i want to select the highest value ID, with the corresponding Currency and Acct Date. 

 

CURRENT

CurrencyAcct DateNet LocalLocal BalanceID
EURO5/1/2019-128.43174239.71
EURO5/10/2019-103350.9770888.731
EURO5/15/2019-70000888.731
EURO5/20/201993900.5594789.281
EURO5/21/2019186823.21281612.491
EURO5/21/2019-281000612.492
U.S. DOLLARS5/1/2019237.35433593.221
U.S. DOLLARS5/2/2019193500627093.221
U.S. DOLLARS5/10/2019-62700093.221
U.S. DOLLARS5/13/201910001093.221
U.S. DOLLARS5/13/2019224060225153.222
U.S. DOLLARS5/15/2019192800417953.221
U.S. DOLLARS5/22/2019-417953.2201
U.S. DOLLARS5/24/2019-212477.77-212477.77

1

 

 

RESULT

CurrencyAcct DateNet LocalLocal BalanceID
EURO5/1/2019-128.43174239.71
EURO5/10/2019-103350.9770888.731
EURO5/15/2019-70000888.731
EURO5/20/201993900.5594789.281
EURO5/21/2019-281000612.492
U.S. DOLLARS5/1/2019237.35433593.221
U.S. DOLLARS5/2/2019193500627093.221
U.S. DOLLARS5/10/2019-62700093.221
U.S. DOLLARS5/13/2019224060225153.222
U.S. DOLLARS5/15/2019192800417953.221
U.S. DOLLARS5/22/2019-417953.2201
U.S. DOLLARS5/24/2019-212477.77-212477.771

 

thank you.

3 REPLIES 3
jasperlch
12 - Quasar

Hi @jdejesus 

 

This can be achieved by first adding a Sort tool by Currency Ascending, then Acct Date Ascending, then ID Descending, followed by a Unique tool where Currency and Acct Date are set as Unique Fields. 

 

Thanks,

Jasper

ChrisTX
16 - Nebula
16 - Nebula

Another option is Summarize and Join

 

Capture1.PNG

 

Capture2.PNG

 

 

jdejesus
7 - Meteor

Thank you! i tested both and both work

Labels
Top Solution Authors