Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Resident statement + Group by

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

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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;

View solution in original post

8 Replies
yduval75
Partner - Creator III
Partner - Creator III

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
Partner - Specialist III
Partner - Specialist III

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
Master III
Master III

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

Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

Brilliant! This Solution is perfect for me!

Thank you Anbu