Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
maxgro
MVP
MVP

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;

Gysbert_Wassenaar

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
Not applicable
Author

Hi Gysbert,

The above senario is not working in my case

can we do a concatnate instead of join there

its_anandrjs

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




its_anandrjs

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

Not applicable
Author

No luck anand can i try it in set expression

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

its_anandrjs

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