Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

count of values using group by

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

7 Replies
Highlighted
MVP
MVP

Re: count of values using group by

X:

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;

Highlighted
MVP & Luminary
MVP & Luminary

Re: count of values using group by

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

;


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: count of values using group by

Hi Gysbert,

The above senario is not working in my case

can we do a concatnate instead of join there

Highlighted

Re: count of values using group by

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




Highlighted

Re: count of values using group by

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

Highlighted
Not applicable

Re: count of values using group by

No luck anand can i try it in set expression

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

Highlighted

Re: count of values using group by

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