How to find max in Column A and keep value from Column B for that maximum

[Lazar1]

Hi,

I need help regarding following question:

How to find max  for KEY in column Week and keep the value from column Status for that maximum?

So my table looks like this:

KEY	WEEK	STATUS
1	1	Passed
1	2	Rejected
1	3	In process
2	4	Rejected
2	5	In process
2	6	Passed
3	7	In process
3	8	Rejected
3	9	Passed

and the result should look like:

KEY	MAX(WEEK)	STATUS
1	3	In process
2	6	Passed
3	9	Passed

Thank you in advance.

[eike_s]

Simple solution here would be as a additional measure:

FirstSortedValue(STATUS,-WEEK)

Important the minus before the week to have the most current status.
More information about the function here:
Help | firstsortedvalue 

Depending on how the data model otherwise looks, you should also take the year into account.

[deepanshuSh]

Hi @Lazar1 you can achieve this using group by, Join and max function. 

key:
LOAD
KEY,
"WEEK",
STATUS
FROM [lib://DataFiles/Task_Lookup.xlsx]
(ooxml, embedded labels, table is Sheet3);

right Join
Load

max ("WEEK") as "WEEK"
Resident key
Group by KEY
;

 Try to learn more about the functionalities about each function to understand the results obtained. 

Thanx and please accept it as a solution to help other community members. 

[Lazar1]

Somehow this doesn't work in my model. Even if I rename all the fields and do these lines of code as a separate script basically.

When I create a new application with dummy data like the ones in the example table, I get good results.

I am not sure what is the problem here.

[deepanshuSh]

Can you share the sample data, and what might be the error that you are facing?

[Lazar1]

Hi,

The script is as follows:

Temp1:

LOAD
KeyNew as KeyMaxStatus,
Week as WeekMaxStatus,
"Last status on CTGF-NEW" as MaxStatus

Resident OverdueFilesAll;

right join
Load
max (WeekMaxStatus) as WeekMaxStatus
Resident Temp1
Group by KeyMaxStatus;

The model looks like (only its really Temp1 not Temp2, that was old printscreen😞

And the results:

Good statuses are found, but they are presented for each week in history. 

[deepanshuSh]

The history would be found as there would be multiple key values for the same weeks, so you need to do grouping by both key and week as well, to get the desired result based on my inference from above screenshot.