Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (1)
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: Resident statement + Group by

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;

8 Replies
yduval75
Contributor III

Re: Resident statement + Group by

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;

Re: Resident statement + Group by

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
Valued Contributor III

Re: Resident statement + Group by

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
Honored Contributor III

Re: Resident statement + Group by

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;

Re: Resident statement + Group by

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

Re: Resident statement + Group by

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
Valued Contributor III

Re: Resident statement + Group by

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

Re: Resident statement + Group by

Brilliant! This Solution is perfect for me!

Thank you Anbu

Community Browser