Resident statement + Group by

[#1]

Hi experts

I have the following resident statement:

Load

jobnr,

max(txtnr) as txtnrMax

resident Notiz GROUP BY jobnr;

I would like to add another field let#s call it "x" which contains text. Since with the group by function the fields need a max, min eg, I do not know how to include my field that contains text.

Anybody know a solution to this problem?

Best

Jan

[yduval75]

Hello,

Load

jobnr,

Only(x) as x // Aggregation function will display null if there are more than one value

max(txtnr) as txtnrMax

resident Notiz GROUP BY jobnr;

or 

Load

jobnr,

x,

max(txtnr) as txtnrMax

resident Notiz GROUP BY jobnr, x;

[rubenmarin]

Hi Jan, you can add this field to the group by sentence:

GROUP BY jobnr, xField;

Also, if you don't want to use in the group by, there are another agregation funtions for text like Concat(), MaxString() or MinString():

Load

jobnr,

max(txtnr) as txtnrMax

MaxString(xField) as xField

resident Notiz GROUP BY jobnr;

[marcus_malinow]

A couple of suggestions:

Do you want to aggregate the text field? Perhaps Maxstring, Minstring or Concat might be appropriate.

If you want to retain all values a second load of jobnr, and your text field might be the way to go.

[anbu1984 · Accepted solution]

If txtnr is integer field, then use Firstsortedvalue()

Load

jobnr,

max(txtnr) as txtnrMax,

FirstSortedValue(X,-txtnr) As X, //This retrieves X for max(txtnr) for each jobnr.

resident Notiz GROUP BY jobnr;

[its_anandrjs]

Hi,

You can simply add

Load

jobnr, x,

max(txtnr) as txtnrMax

resident Notiz GROUP BY jobnr, x;

This works very well if you want to add more field do the same

Regards

Anand

[#7]

Hi Marcus

My Plan is to read out the latest entries of my testfiled for every jobnr. there cane be multiple entries...

Best

Jan

[marcus_malinow]

In that case, I'd use something similar to the suggestion by anbu.

firstsortedvalue(X, -Date) for example would get the value of X for the largest value of Date

[#9]

Brilliant! This Solution is perfect for me!

Thank you Anbu