topic Re: group by statement in QlikView

group by statement

Wed, 15 Oct 2014 11:58:43 GMT

Hi Experts

Ich habe the following load-statement and would like to group by jobnr:

SQL SELECT "auf_datum",

bez as Leistungstext,

jobnr,

storniert,

optional,

verkauf_soll as VK_soll,

verkauf_ist as VK_ist,

einkauf_ist as EK_ist,

auf_brutto as EK_soll,

FROM EASY.jleistg

WHERE storniert = 0

and optional = 0;

How do I have to put the "group by jobnr" expression?

Thank you very much for your help!

Jan

Re: group by statement

maleksafa — Wed, 15 Oct 2014 12:01:42 GMT

when you are using sql statement you can use the group by function after the where clause, in your case add group by jobnr after the where clause but you need to add some aggregation on the other fields for example

select max("auf_datum"), jobnr from EASY.jleist where storniert = 0 group by jobnr

the column in the select must either have an aggregation (max, min, sum, avg etc...) or included in the group by

Re: group by statement

Wed, 15 Oct 2014 12:04:11 GMT

Okay, thank you. You mean like this:

FROM EASY.jleistg

WHERE storniert = 0

and optional = 0 group by jobnr;

Thank you

Re: group by statement

MarcoWedel — Wed, 15 Oct 2014 12:04:21 GMT

Hallo Jan,

grouping only applies to aggregations.

Do you also plan to add a sum, count or other aggregating function?

Gruß Marco

Re: group by statement

Wed, 15 Oct 2014 12:08:10 GMT

YEs, I do.

Re: group by statement

maxgro — Wed, 15 Oct 2014 12:25:13 GMT

fields not in group by needs a sum, count, .... (aggregation)

so you have to add some function for all bold fields

example sum(storniert) or count(storniert)

SQL SELECT

"auf_datum",

bez as Leistungstext,

jobnr,

storniert,

optional,

verkauf_soll as VK_soll,

verkauf_ist as VK_ist,

einkauf_ist as EK_ist,

auf_brutto as EK_soll

FROM EASY.jleistg

WHERE storniert = 0

and optional = 0

group by jobnr

;