Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!
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