Max and Group by

[hakandikmen]

Hello,

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

how can I do it? thanks.

[martinpohl · Accepted solution]

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

[Or]

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

[md_talib]

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;

[hakandikmen]

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?

[robert99]

Use left keep