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: 
jaarroyl
Contributor III
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
sunny_talwar

Try this as your expression

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

View solution in original post

14 Replies
sunny_talwar

May be this

Dimension

ID

Expression

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

nithin_miryala
Creator
Creator

Something like this may.

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

jaarroyl
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

sunny_talwar

Try this as your expression

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

jaarroyl
Contributor III
Contributor III
Author

Thanks you very much as always.

Regards from Chile

jaarroyl
Contributor III
Contributor III
Author

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

sunny_talwar

Try this

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

jaarroyl
Contributor III
Contributor III
Author

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