topic Re: Select values based on possible values in another table in QlikView

Select values based on possible values in another table

Tue, 13 Nov 2012 20:40:16 GMT

I have a table of Supplier Names (island table) to use in Set Analysis. this table is reduced on load based on section access.

I use set analysis to sum sales where the Manufacturer = Supplier Name. But the user can pick multiple suppliers and they can opt to default to all Supplier Names.

how do I limit the Manufacturers to only Those Supplier Names that were loaded using Section Acccess.

we cannot reduce Manufacturer due to the way they linked the datasources. Unfortunately it reduces a couple other tables we do not want reduced.

Manufacturer

MF1

MF2

MF3

MF4

Supplier Name (island table)

MF1

MF2

MF3

MF4

MF5

section access reduces user 1 to just MF1, MF2, MF4

In expresssion i need to sum({<Manufacturer = Any available Supplier Name>} Sales)

Is this possible?

Re: Select values based on possible values in another table

swuehl — Tue, 13 Nov 2012 21:06:10 GMT

Try something like

sum({<Manufacturer *= p([Supplier Name]) >} Sales)

Re: Select values based on possible values in another table

Tue, 13 Nov 2012 21:48:17 GMT

the *= did not work but it works with just =. thank you. The first time I tried this it did not work but I think it was my section access settings, not the set analysis

Re: Select values based on possible values in another table

IAMDV — Wed, 14 Nov 2012 12:09:20 GMT

Hi,

I like the idea of using data island as field reduction in Section Access. But I think you can still re-model the data and make it to work, but you know it better than me. However, there is potential risk of missing an expression calculation to intersect with the reduced data island…which means user will have full access. And also think about AJAX client deployment where users can create their own charts and write the expression. How do you overcome these issues? I strongly feel that you need much stronger approach to solve this issue. As mentioned above if you can’t change the data model… here are few more things you can consider.

How about using Publisher with Loop & Reduce Functionality?
IF you don’t have Publisher then you can mimic same functionality by using (File >> Reduce Data >> Keep Only Possible Values). And this approach works if there is less cardinality of data on your data island

I’m sure you know what you’re doing but security is very sensitive…so I’m adding views.

Thanks,

www.QlikShare.com