topic Re: How to Count and Group By in QlikView

How to Count and Group By

jaarroyl — Tue, 13 Feb 2018 20:17:22 GMT

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

Re: How to Count and Group By

sunny_talwar — Tue, 13 Feb 2018 20:22:14 GMT

May be this

Dimension

Expression

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

Re: How to Count and Group By

nithin_miryala — Tue, 13 Feb 2018 20:23:16 GMT

Something like this may.

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

Re: How to Count and Group By

jaarroyl — Tue, 13 Feb 2018 20:45:08 GMT

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

Re: How to Count and Group By

jaarroyl — Tue, 13 Feb 2018 20:45:25 GMT

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

sunny_talwar — Tue, 13 Feb 2018 20:46:49 GMT

Try this as your expression

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

Re: How to Count and Group By

jaarroyl — Tue, 13 Feb 2018 20:48:25 GMT

Thanks you very much as always.

Regards from Chile

Re: How to Count and Group By

jaarroyl — Thu, 15 Feb 2018 13:12:35 GMT

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

sunny_talwar — Thu, 15 Feb 2018 14:24:34 GMT

Try this

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

Re: How to Count and Group By

jaarroyl — Thu, 15 Feb 2018 14:31:52 GMT

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

Re: How to Count and Group By

sunny_talwar — Thu, 15 Feb 2018 14:37:55 GMT

Try this

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

Re: How to Count and Group By

jaarroyl — Thu, 15 Feb 2018 14:52:15 GMT

It didnt work.

The same before.

Re: How to Count and Group By

sunny_talwar — Thu, 15 Feb 2018 15:06:37 GMT

It seems to be working for me...

Without selection

INVALID Selected

VALID Selected

Re: How to Count and Group By

jaarroyl — Thu, 15 Feb 2018 16:32:33 GMT

It Works, you rigth. Thanks you

Re: How to Count and Group By

sunny_talwar — Thu, 15 Feb 2018 16:34:12 GMT

okay nice