topic Re: Max and Group by in App Development

Max and Group by

hakandikmen — Mon, 21 Aug 2023 10:44:15 GMT

Hello,

I can't write the Script code (Load) that will provide the output below.

how can I do it? thanks.

Re: Max and Group by

martinpohl — Mon, 21 Aug 2023 11:26:17 GMT

Hi,

you want to do this in script?

YourData:

load

all your fields

from [lib://source];

left join load

max(CompletedDate) as MaxDate,

IssueNo

resident YourData group by IssueNo;

MaxData:

noconcatenate load * resident YourData where CompletedDate = MaxDate;

drop table YourData;

Maybe you have to check your table after left join that number format of Maxdate is correct number format of CompletedDate, if not set timestamp().

Regards

Re: Max and Group by

Or — Mon, 21 Aug 2023 11:38:26 GMT

Have a look at using FirstSortedValue() for this. It should be exactly what you need.

Re: Max and Group by

md_talib — Mon, 21 Aug 2023 11:50:07 GMT

IF you wan to get the max value from the status.

Use this

Table:

Load

CreatedDate,

CompletedDate,

DemandOwner,

IssueNo,

IsuueType,

Responsible,

Status,

CreatedDate&'-'&IssueNo as KEY,

from datasource;

left join

Load

KEY,

Status,

max(CreatedDate) as MaxTime,

resident Table

group by Status,KEY;

Re: Max and Group by

hakandikmen — Mon, 21 Aug 2023 13:16:14 GMT

Thanks, it worked.

So how can we do this as a separate table.

Without using Left Join. Max and its data keep it in that table..

Can you help with this?

Re: Max and Group by

robert99 — Mon, 21 Aug 2023 13:29:58 GMT

Use left keep