topic count of values using group by in QlikView

count of values using group by

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have a senario where my table is as below

Mytable:

LOAD

date,

id,

sales,

kesr,

region,

country,

employee,

order

now i want to do a

count(id) as NewCount

group by

region,

country,

employee,

order

and my final table should also contains other fields as well

as show below

Mytable:

date,

id,

NewCount,

sales,

kesr,

region,

country,

employee,

order

how can i achieve it

Re: count of values using group by

maxgro — Wed, 16 Nov 2016 17:49:40 GMT

LOAD

date,

id,

sales,

kesr,

region,

country,

employee,

order

from

.....;

left join (X)

load

region,

country,

employee,

order,

count(id) as NewCount

resident X

group by

region,

country,

employee,

order;

Re: count of values using group by

Gysbert_Wassenaar — Wed, 16 Nov 2016 17:52:25 GMT

Result:

LOAD

date,

id,

sales,

kesr,

region,

country,

employee,

order

FROM

...souce...

;

JOIN (Result)

LOAD

region,

country,

employee,

order

count(id) as NewCount

group by

region,

country,

employee,

order

RESIDENT

Result

;

Re: count of values using group by

Wed, 16 Nov 2016 19:14:06 GMT

Hi Gysbert,

The above senario is not working in my case

can we do a concatnate instead of join there

Re: count of values using group by

its_anandrjs — Wed, 16 Nov 2016 19:18:59 GMT

You can try this also and one possible way as well

FinalTable:

LOAD

region & country & employee & order as Key

date,

id,

sales,

kesr,

region,

country,

employee,

order

From Table;

Left Join( FinalTable )

Load

region & country & employee & order as Key

count(id) as NewCount

Resident FinalTable

group by

region & country & employee & order ;

Regards

Anand

Re: count of values using group by

its_anandrjs — Wed, 16 Nov 2016 19:25:48 GMT

In your scenario here concatenate is not working because it is same table and you need the aggregation of the data on the some selected values that are four fields

region,

country,

employee,

order

In this way you need on aggregation of the id

Regards,

Anand

Re: count of values using group by

Wed, 16 Nov 2016 19:48:19 GMT

No luck anand can i try it in set expression

count(aggr(count)............ some thing like this

Re: count of values using group by

its_anandrjs — Wed, 16 Nov 2016 19:53:16 GMT

Yes you can do this aggregation in the expression also

Ex:-

Count(Aggr(Count ( id ),region,country,employee,order))

But the suggested aggregation on the script level is also correct why it is not working...

Regards

Anand