Filter distinct data (aggr, count, pivot)

ivanbrewer — Thu, 10 May 2018 11:02:02 GMT

Hi All,

I have country / customer / sku data and I need to build a tool to visually compare prices within the same country by customer (for non zero non missing items). Could you please help with the following illustrative scenario:

Input data:

Input data comes as a result of joining two tables: a table with country x customer x sku mapping + another table with price details.

Required output: pivot table - for any selected countries - SKU in the rows / customers in the columns - show only lines where there are at least two DIFFERENT non-zero prices:

I've tried to achieve it with countr(aggr(distinct)), but struggling to make it work for all situations.

Please see enclosed the qvw - any suggestions much appreciated!

Thank you,

Regards

Re: Filter distinct data (aggr, count, pivot)

sunny_talwar — Thu, 10 May 2018 14:33:36 GMT

Try this

Sum({<sku = {"=Count({<price -= {'0'}>}DISTINCT price) > 1"}>} price)

Re: Filter distinct data (aggr, count, pivot)

ivanbrewer — Thu, 10 May 2018 21:25:48 GMT

Hi Sunny,

Thanks a lot stalwar1, your suggestion has solved it and I've applied the same approach to few other cases that I had, thanks.

Regards

topic Filter distinct data (aggr, count, pivot) in QlikView

Filter distinct data (aggr, count, pivot)

Re: Filter distinct data (aggr, count, pivot)

Re: Filter distinct data (aggr, count, pivot)