topic Re: Resident statement + Group by in QlikView

Resident statement + Group by

Fri, 05 Dec 2014 10:06:52 GMT

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

Re: Resident statement + Group by

yduval75 — Fri, 05 Dec 2014 10:09:28 GMT

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;

Load

jobnr,

max(txtnr) as txtnrMax

resident Notiz GROUP BY jobnr, x;

Re: Resident statement + Group by

rubenmarin — Fri, 05 Dec 2014 10:10:21 GMT

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;

Re: Resident statement + Group by

marcus_malinow — Fri, 05 Dec 2014 10:12:02 GMT

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.

Re: Resident statement + Group by

anbu1984 — Fri, 05 Dec 2014 10:13:12 GMT

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

its_anandrjs — Fri, 05 Dec 2014 10:13:24 GMT

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

Re: Resident statement + Group by

Fri, 05 Dec 2014 10:36:49 GMT

Hi Marcus

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

Best

Jan

Re: Resident statement + Group by

marcus_malinow — Fri, 05 Dec 2014 10:39:48 GMT

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

Re: Resident statement + Group by

Fri, 05 Dec 2014 10:52:36 GMT

Brilliant! This Solution is perfect for me!

Thank you Anbu