Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

Community Browser