topic Re: How to find max in Column A and keep value from Column B for that maximum in App Development

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

Lazar1 — Tue, 27 Sep 2022 16:04:02 GMT

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.

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

eike_s — Tue, 27 Sep 2022 16:36:39 GMT

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.

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

deepanshuSh — Tue, 27 Sep 2022 16:47:09 GMT

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.

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

Lazar1 — Mon, 03 Oct 2022 08:40:46 GMT

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.

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

deepanshuSh — Tue, 04 Oct 2022 16:17:21 GMT

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

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

Lazar1 — Wed, 05 Oct 2022 08:04:48 GMT

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.

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

deepanshuSh — Wed, 05 Oct 2022 16:18:48 GMT

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.