Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
jaarroyl
New Contributor III

How to Count and Group By

Hello World!

Please I need help to get the number of valid products (State) that a customer has on the company and group by ID

I have this table

ID -  PRODUCTS ID - STATE

15363 - VEHICLE - VALID

15363 - MOTO - INVALID

15363 - VEHICLE 2 - VALID

14321 - VEHICLE - INVALID

14321 - VEHICLE 2 - VALID

I need to get this

ID - NUMBER OF PRODUCT VALID

15363 - 2

14321 - 1

Thanks

1 Solution

Accepted Solutions

Re: How to Count and Group By

Try this as your expression

Count(TOTAL <ID> {<STATE = {'VALID'}>} ID)

14 Replies

Re: How to Count and Group By

May be this

Dimension

ID

Expression

Count({<STATE = {'VALID'}>} ID)

nithin_miryala
Contributor

Re: How to Count and Group By

Something like this may.

=Count({<State={'VALID'}>}ID)

jaarroyl
New Contributor III

Re: How to Count and Group By

Sunny, hello

I have tried that. Maybe I didnt explain as well I though.

I need to get this

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 2

15363 - MOTO - INVALID - 2

15363 - VEHICLE 2 - VALID - 2

14321 - VEHICLE - INVALID - 1

14321 - VEHICLE 2 - VALID - 1

And when I did that, I got

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 1

15363 - MOTO - INVALID - 0

15363 - VEHICLE 2 - VALID - 1

14321 - VEHICLE - INVALID - 0

14321 - VEHICLE 2 - VALID - 1

Got it?

Sorry

jaarroyl
New Contributor III

Re: How to Count and Group By

The same for Sunny.

thanks for your help

I have tried that. Maybe I didnt explain as well I though.

I need to get this

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 2

15363 - MOTO - INVALID - 2

15363 - VEHICLE 2 - VALID - 2

14321 - VEHICLE - INVALID - 1

14321 - VEHICLE 2 - VALID - 1

And when I did that, I got

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 1

15363 - MOTO - INVALID - 0

15363 - VEHICLE 2 - VALID - 1

14321 - VEHICLE - INVALID - 0

14321 - VEHICLE 2 - VALID - 1

Got it?

Sorry

Re: How to Count and Group By

Try this as your expression

Count(TOTAL <ID> {<STATE = {'VALID'}>} ID)

jaarroyl
New Contributor III

Re: How to Count and Group By

Thanks you very much as always.

Regards from Chile

Highlighted
jaarroyl
New Contributor III

Re: How to Count and Group By

Sunny,

I have another problem.

I got this:

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 1

15363 - MOTO - INVALID - 0

15363 - VEHICLE 2 - VALID - 1

14321 - VEHICLE - INVALID - 0

14321 - VEHICLE 2 - VALID - 1

And when I try to filter by a list box with STATE as filter, the table shows the data without considering the filter.

Check it out.

stalwar1

Re: How to Count and Group By

Try this

Count(TOTAL <ID> {<STATE *= {'VALID'}>} ID)

jaarroyl
New Contributor III

Re: How to Count and Group By

Works, but when the filter goes the count is equal 0.

For example, when I filter by VALID, all the counts in INVALID goes to 0.

Withou Filter:

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID - NUMER OF PRODUCT INVALID

15363 - VEHICLE - VALID - 2 - 1

15363 - MOTO - INVALID - 2 - 1

15363 - VEHICLE 2 - VALID - 2 - 1

14321 - VEHICLE - INVALID - 1 - 1

14321 - VEHICLE 2 - VALID - 1 - 1

Filter by: INVALID

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID - NUMER OF PRODUCT INVALID

15363 - MOTO - INVALID - 0 - 1

14321 - VEHICLE - INVALID - 0 - 1

Flter by: VALID

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID - - NUMER OF PRODUCT INVALID

15363 - VEHICLE - VALID - 2 - 0

15363 - VEHICLE 2 - VALID - 2 - 0

14321 - VEHICLE 2 - VALID - 1 - 0