topic Re: Section access with wildcards in App Development

Section access with wildcards

mborsadw — Thu, 15 Dec 2016 15:47:22 GMT

From one of the threads (section access and application access)

Example:

This table you would interpret as "A can see the data if (Shop=X and Product=ANY) or (Shop=ANY and Product=Y)". But QlikView interprets it the other way around: "A can see data if (Shop=X or Shop=ANY) and (Product=ANY or Product=Y)". So it won't work. A will be able to see everything.

This blog explains how to achieve this using concatenation of the field values and a Bridge which is great!

Is there a way to support wildcards such as below:

USER	%AUTHID
A	X\|P*
A	S*\|Y

In query terms it would be something like :

(shop=X and product like 'P%') or (shop like 'S%' and product = Y)

Regards,

Mufaddal

Re: Section access with wildcards

thomaslg_wq — Thu, 15 Dec 2016 16:39:27 GMT

Hi,

Well, you may have to create your own script doing this in the bridge.

Something with a loop like this:

// let's imagine your section access :

Security:

LOAD * INLINE [

USER, SECURITY_LINK

ABC, A/P*

];

// At the end of the script = the bridge

For i = 0 to noofrows('Security')-1

let vShop=peek('SHOP',i,'Security');

let vProduct=peek('PRODUCT',i,'Security');

Bridge:

LOAD

Fact_Shop&'/'&Fact_Product as Fact_Link,

Fact_Shop&'/'&Fact_Product as SECURITY_LINK

resident FACT_Table

where wildmatch(Fact_Shop,'$(vShop)') and wildmatch(Fact_Product,'$(vProduct)';

next i

Re: Section access with wildcards

mborsadw — Thu, 15 Dec 2016 18:12:58 GMT

Is Looping the only way ? Is it going to be a performance bottleneck assuming I have a couple of million of records in the Fact table.

Can I do a 'Where Exists(Field)' with a wildmatch? Kind of like 'WildmatchExists(Field)'?

Re: Section access with wildcards

maxgro — Thu, 15 Dec 2016 19:32:51 GMT

try the attachment, hope to understand your question

Re: Section access with wildcards

thomaslg_wq — Fri, 16 Dec 2016 14:43:39 GMT

Even if there were a where exists wildmatch(Field) you would have to do a loop on the security tab values to create a real link with the user.

Try my solution and check if this is ok with time duration.

Try also the maxgro's solution that is really nice but seems to be working only when you want to use the star only after the first letter of your value, like P* and not PAM*

Re: Section access with wildcards

mborsadw — Fri, 16 Dec 2016 17:46:17 GMT

Thanks for your reply maxgro. Your solution taught me quite a few new things about Qlik Scripting.

But as Thomas mentions it only works when the wildcard character * is after the first letter like P*. It does not work for PAM* or just *

Re: Section access with wildcards

mborsadw — Fri, 16 Dec 2016 17:47:09 GMT

Let me try the loop solution that you presented. Thanks!