topic Re: Set Analysis, multiple selection from Table in QlikView

Set Analysis, multiple selection from Table

Sat, 07 Feb 2015 00:34:37 GMT

Hi,

I would like to apply a filter to a SUM based on a value from a table..

TABLE A:

ACCOUNT	PRODUCT	SALES
A001	P001	100
A001	P001	100
A001	P002	100
A001	P002	100
A001	P003	100
A001	P006	100
A001	P007	100

TABLE B:

EMP	ACCOUNT	PAY-ON
E001	A001	P001,P002
E002	A001	P001,P003,P004

RESULT:

EMP	ACCOUNT	PAY-ON	SALES
E001	A001	P001,P002	400
E002	A001	P001,P003,P004	300

I have thousands of accounts and hundreds of emp and Products, any idea?

Sum({ PRODUCT = "VPAY-ON" } Sales)

Thanks

Re: Set Analysis, multiple selection from Table

Oleg_Troyansky — Sat, 07 Feb 2015 01:04:36 GMT

Set Analysis won't help your problem... Instead, you should perform some data modeling and transform your two tables into associated tables that link each employee to the corresponding customers and products.

1. Table B needs to be "normalized" using the SubField() function, so that each row contains a single product.

2. In order to avoid a synthetic key, I'd recommend creating a combo key that contains Accounts and Products. This key will be the link between the two tables. It should, however, work fine even with the synthetic key.

3. Then, your chart could be configured like this:

Dimensions: Employee, Account

Expressions:

Pay-On: concat(Product, ',')

Sales: sum(Sales)

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Set Analysis, multiple selection from Table

Sat, 07 Feb 2015 01:15:57 GMT

Thanks, I figure out by adding a column to table A and adding a 3rd table

TABLE A

EMP	ACCOUNT	PAY-ON	CT
E001	A001	P001,P002	C001
E002	A001	P001,P003,P004	C002

TABLE C

PRODUCT	PCT
P001	C001
P002	C001
P003	C002
P001	C002
P004	C002

And apply a filter PCT = CT