topic Re: section access more than one field in QlikView

section access more than one field

gauthamchilled — Mon, 22 Feb 2016 13:28:09 GMT

Hi,

I have gone thru HIC's blog on section access reduction in more than one field. Not sure how to build the following auth keys for all possible combinations.

how to get all possible combinations for the below like

COMPA	COMPB	COMPC	COMPD
1	1	1	ANY
1	1	ANY	ANY

- - - -

i know the script for this to concatnate and get the auth key for the bridge table. but how to generate the combinations easily..

not sure how many combinations it will have also. please help.

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 13:51:36 GMT

JUST a help to generate all key combinations to create auth key.anyone help?

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 13:59:37 GMT

Hi,

Can't you just do a resident load from the fact table to get all possible values for each field?

Cesar

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 14:00:16 GMT

A load 'distinct' should work

Re: section access more than one field

Chanty4u — Mon, 22 Feb 2016 14:02:42 GMT

what ever the section access script hve u can load and do resident load.

u will get those as per requirmnt

and all shud be in Upper case.

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 14:27:07 GMT

But HIC is doing multiple times

in generic keys pdf attached page no 13

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 14:28:43 GMT

since i am using 4 fields reduction. i cannot use section access script table directly. I am creating authorization table and auth bridge table. so confusion to get all possible key combinations

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 14:57:34 GMT

Hi,

If you have 4 reduction fields then you have to load 4 times:

LOAD DISTINCT

COMPA & '|<ANY>|<ANY>|<ANY>' as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT;

LOAD DISTINCT

'<ANY>|' & COMPB & '|<ANY>|<ANY>' as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT;

LOAD DISTINCT

'<ANY>|<ANY>|' & COMPC & '|<ANY>' as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT;

LOAD DISTINCT

'<ANY>|<ANY>|<ANY>|' & COMPD as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT;

And you create %FactKey on the fact table as well to do the linkage.

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 15:00:22 GMT

You can add a WHERE clause to each loading as well to reduce the size of the Authorisation bridge table:

LOAD DISTINCT

COMPA & '|<ANY>|<ANY>|<ANY>' as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT WHERE NOT ISNULL(COMPA);

LOAD DISTINCT

'<ANY>|' & COMPB & '|<ANY>|<ANY>' as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT WHERE NOT ISNULL(COMPB);

LOAD DISTINCT

'<ANY>|<ANY>|' & COMPC & '|<ANY>' as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT WHERE NOT ISNULL(COMPC);

LOAD DISTINCT

'<ANY>|<ANY>|<ANY>|' & COMPD as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT WHERE NOT ISNULL(COMPD);

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 15:02:30 GMT

OK.

so only 4 combinations?

'<ANY>|<ANY>|<ANY>|<ANY>' is not there?

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 15:07:05 GMT

hic‌ can you please help? Is it only 4 combinations we get if we have reduce for 4 fields?

Is it not require to load all possible key combinations for section using multiple fields?

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 15:11:23 GMT

That is a good point, you should add that key as well:

LOAD DISTINCT

'<ANY>|<ANY>|<ANY>|<ANY>' as %AuthKey,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT;

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 15:13:28 GMT

COMPA & '|<ANY>|<COMPC>|<ANY>' as %AuthKey? THERE ARE many key combinations right?

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 15:19:14 GMT

When you concatenate COMPA with <ANY>|<ANY>|<ANY> it means the list of values for COMPA plus any given value for the other fields, so that already includes COMPA & '|<ANY>|<COMPC>|<ANY>'.

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 15:32:25 GMT

Sorry, in your access table when it says COMPA = 1, COMPB = 1, etc... Does that mean:

COMPA = 1 AND COMPB = 1?
COMPA = 1 OR COMPB = 1?

Because the load script I provided is for #2.

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 15:36:57 GMT

COMPA = 1 AND COMPB = 1

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 15:37:18 GMT

OH..what is for case 1 then?

Re: section access more than one field

gauthamchilled — Mon, 22 Feb 2016 15:53:32 GMT

anyone help?

Re: section access more than one field

luciancotea — Mon, 22 Feb 2016 15:54:10 GMT

If you have '_' for 'ANY'

and

X for each COMP value

then

Load XXXX as Key from Transactions

Load _XXX as Key from Transactions

Load X_XX as Key from Transactions

Load XX_X as Key from Transactions

Load XXX_ as Key from Transactions

Load __XX as Key from Transactions

Load XX__ as Key from Transactions

Load _XX_ as Key from Transactions

Load X_X_ as Key from Transactions

Load _X_X as Key from Transactions

Load X__X as Key from Transactions

Load ___X as Key from Transactions

Load __X_ as Key from Transactions

Load _X__ as Key from Transactions

Load X___ as Key from Transactions

Load ____ as Key from Transactions

After that, to exclude all extra combinations, you reload again the result using a method to reduce the rows (KEEP, EXISTS, INNER JOIN,...)

Re: section access more than one field

cesaraccardi — Mon, 22 Feb 2016 16:03:47 GMT

In that case you can load the concatenation of keys from Fact table and do a Cartesian join with the keys from the section access table (this can generate a very large table depending on the number of values you have in your data set but will have all possible combinations against each key):

TMP_KEYS:

LOAD DISTINCT

COMPA as FACT_COMPA,

COMPB as FACT_COMPB,

COMPC as FACT_COMPC,

COMPD as FACT_COMPD,

COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD as %FactKey

RESIDENT FACT;

JOIN(TMP_KEYS)

LOAD

COMPA as ACCESS_COMPA,

COMPB as ACCESS_COMPB,

COMPC as ACCESS_COMPC,

COMPD as ACCESS_COMPD,

%AuthKey

RESIDENT SECTION_ACCESS;

Now you do a resident load and get rid of the keys you are not interested (ANY values):

BRIDGE:

LOAD

%FactKey,

%AuthKey

RESIDENT

TMP_KEYS

WHERE

(FACT_COMPA = ACCESS_COMPA OR ACCESS_COMPA = 'ANY') AND

(FACT_COMPB = ACCESS_COMPB OR ACCESS_COMPB = 'ANY') AND

(FACT_COMPC = ACCESS_COMPC OR ACCESS_COMPC = 'ANY') AND

(FACT_COMPD = ACCESS_COMPD OR ACCESS_COMPD = 'ANY');

DROP TABLE TMP_KEYS;