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: 
Not applicable

Set Analysis, multiple selection from Table

Hi,

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

TABLE A:    

ACCOUNTPRODUCTSALES
A001P001100
A001P001100
A001P002100
A001P002100
A001P003100
A001P006100
A001P007100

TABLE B:

EMPACCOUNTPAY-ON
E001A001P001,P002
E002A001P001,P003,P004

RESULT:

EMPACCOUNTPAY-ONSALES
E001A001P001,P002400
E002A001P001,P003,P004300

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

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

Thanks

1 Solution

Accepted Solutions
Not applicable
Author

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

TABLE A

EMPACCOUNTPAY-ONCT
E001A001P001,P002C001
E002A001P001,P003,P004C002

TABLE C

PRODUCTPCT
P001C001
P002C001
P003C002
P001C002
P004C002

And apply a filter PCT = CT

View solution in original post

2 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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!

Not applicable
Author

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

TABLE A

EMPACCOUNTPAY-ONCT
E001A001P001,P002C001
E002A001P001,P003,P004C002

TABLE C

PRODUCTPCT
P001C001
P002C001
P003C002
P001C002
P004C002

And apply a filter PCT = CT