topic Re: Visibility_condition for admins in QlikView

Visibility_condition for admins

datanibbler — Mon, 15 Dec 2014 09:27:22 GMT

Hi,

I have the requirement of putting into a variable an expression referring to a variable number of values:

- There are 2 QlikView_admins.

- For those 2 I have, in each app, a button governing a second variable v_Dev
=> This variable will turn on/off the visibility of several objects like rulers, comments etc. that are relevant for development, but not for

the users.

I don't want to have these 2 names hard-coded in every app, but just once in a central file.

I have already tried with a Partial_reload_scenario, but that didn't work out.

So what I want to do now is the following:

- In the script, I will load that Excel_file with the names of all the QlikView_admins and generate the logons I need.

- I can create one variable for every line in that table - currently 2, but there might be more going forward.

=> The next step is where it fails: I want to create the entire statement for my visibility_condition based on those variables.

The code currently looks like this (I attach a sample xls that looks just like this, but in xls, not xlsx):

Developers_pre:
LOAD
Recno() as Line_ID_Admin,
Domain,
Name,
(Upper(Domain) & '\' & Lower(Name)) as OSU
FROM
[\\rgb1app202\production\99_Resources\Wichtig_nicht_löschen\QlikView_Admins.xl*]
(ooxml, embedded labels, table is Names);

//Jetzt gehen wir diese Tabelle Zeile für Zeile durch und generieren Variablen.
LET v_admins = NoOfRows('Developers_pre');
LET e_admins_only = '_';
FOR i = 1 TO $(v_admins)
LET varname = 'v_admin_' & $(i);
LET $(varname) = PEEK('OSU', ($(i)-1), Developers_pre);
LET e_admins_only = $(e_admins_only) & ' OR OSUSER() = ' & chr(39)&$(varname)&chr(39);
NEXT

This loop works in the first iteration, my variables look all right and that expression e_admins_only is properly constructed - in the first iteration. For some reason, it fails in the second one.

When you leave out that expression, it runs in 2 iterations.

Can anybody help me with that?

Thanks a lot!

Best regards,

DataNibbler

Re: Visibility_condition for admins

Gysbert_Wassenaar — Mon, 15 Dec 2014 10:50:07 GMT

Maybe like this: LET e_admins_only = '$(e_admins_only)' & ' OR OSUSER() = ' & chr(39)&$(varname)&chr(39);

Re: Visibility_condition for admins

datanibbler — Mon, 15 Dec 2014 10:59:58 GMT

Great!

I didn't know you could put that $() thing into quotes and QlikView would still evaluate it.

Now it works - now I only have to trim it and we'll see.

Thanks a lot!

Best regards,

DataNibbler

Re: Visibility_condition for admins

Peter_Cammaert — Mon, 15 Dec 2014 11:09:44 GMT

Actually, $-substitution is even more powerful. Whenever the script engine encounters a new statement (terminated by EOL or a semicolon), it will launch a preprocessor that will perform $-sign substitution on this single statement. That means that all pieces of text that look like $(xxxx) will be replaced by their textual counterparts, BEFORE the statement will be evaluated. Even better, this replacement is performed recursively, so that artefacts like $(MyVariable$(MyVariableNumber)) will first evaluate the inner value, add the text to the name of the outer variable, and only then replace that one with the corresponding text.

And this works weherever you put a $(...), even on the right side of a SET statement, inside a label between square brackets, in text strings, everywhere.

Pretty powerful stuff.

Re: Visibility_condition for admins

datanibbler — Mon, 15 Dec 2014 11:28:59 GMT

Hi,

now I have only one last - but no less annoying - issue with the quotes:

Due to this method, there are at first 2 single quotes around the first logon and single ones around the second logon. When I trim this (to get rid of what I put into the dummy before entering the loop, there are 4 single quotes around the first logon, 2 in front of the second logon - but only 1 at the end of the second logon !?

Can you help me once more there?

I post the code here once again (you can use the file I attached in the last post)

Developers_pre:
LOAD
Recno() as Line_ID_Admin,
Domain,
Name,
(Upper(Domain) & '\' & Lower(Name)) as OSU
FROM
[\\rgb1app202\production\99_Resources\Wichtig_nicht_löschen\QlikView_Admins.xl*]
(ooxml, embedded labels, table is Names);

LET v_admins = NoOfRows('Developers_pre');

LET e_admins_only = ' a_ ';

FOR i = 1 TO $(v_admins)
LET varname = 'v_admin' & $(i);
LET $(varname) = PEEK('OSU', ($(i)-1), Developers_pre);
LET varname2 = 'e_admins_only_part' & $(i);
LET $(varname2) = 'OSUSER() = ' & chr(39) & $(varname) & chr(39);
LET e_admins_only = '$(e_admins_only)' & ' OR OSUSER() = ' & chr(39) & $(varname) & chr(39);
// Jetzt löschen wir die Teilvariable gleich wieder
SET varname2 = ;
SET varname = ;
NEXT i;

// Jetzt müssen wir das Anfangsstück vom Dummy abschneiden
LET e_admins_only_v2 = TRIM(MID('$(e_admins_only)', 8, (LEN('$(e_admins_only)')-8)));

Re: Visibility_condition for admins

Peter_Cammaert — Mon, 15 Dec 2014 12:41:04 GMT

Change the very last 8 in the last line into a 7.

If you keep your string from position 8 onwards, that means you're cutting off 7 characters from the front. Not 8. Indices in QV start from position 1.

Peter

Re: Visibility_condition for admins

datanibbler — Mon, 15 Dec 2014 12:59:47 GMT

Hi Peter,

thanks! But that's not my primary issue:

My primary issue is that I have too many single quotes:

- Every part of the statement needs single quotes around the logon:

>>> OSUSER() = '[domain\username]' <<<

and I have one pair of single quotes around the $() substitution in the 2nd iteration - so I have two single quotes around the first logon (actually, there should be one in front and two at the end, no?

=> I have tried by replacing chr(39) with chr(33) - that is an exclamation mark.

=> That works fine, but then - how can I write a REPLACE() statement at the end of the whole story to again replace the exclamation_marks by single quotes?`I cannot put a single quote into single quotes ...

Best regards,

DataNibbler

Re: Visibility_condition for admins

Peter_Cammaert — Mon, 15 Dec 2014 13:22:28 GMT

Yes, I understand your worry. But then, in my document which uses an exact copy of your script (except for the number 7 that I like a little more) this is what comes out of your script in variable e_admins_only_v2:

OSUSER() = 'SYNCREON\friedrich.hofmann' OR OSUSER() = 'SYNCREON\sven.arnold'

Although you might think that the first expansion will produce too many quotes and the script engine will fall over next time around, it still works... This is what comes out when I add a few administrators

OSUSER() = 'SYNCREON\friedrich.hofmann' OR OSUSER() = 'SYNCREON\sven.arnold' OR OSUSER() = 'DISNEY\mickey.mouse' OR OSUSER() = 'DISNEY\donald.duck' OR OSUSER() = 'PIXAR\mister.incredible'

Re: Visibility_condition for admins

Peter_Cammaert — Mon, 15 Dec 2014 13:26:42 GMT

I should tell you that I'm using 11.20SR9, and consequently your mileage may vary. That is if I correctly remember our recent discussions that focused on an older QVS installation.

Re: Visibility_condition for admins

datanibbler — Mon, 15 Dec 2014 13:28:00 GMT

Hi Peter,

that is very strange. I have different results in my code:

- In the 1st iteration, there is one logon with one pair of single quotes around it

- In the 2nd iteration, there are 2 pairs of single quotes around the 1st logon and one pair around the 2nd logon

=> In e_admins_only_v2, there are

- 4 pairs of single quotes around the 1st logon

- 2 single quotes in front of the 2nd logon

- 1 single quote at the end of the 2nd logon

That is very strange - even more so because anything else (I've tried the ! instead of single quotes around the logon in the individual parts of the expression - does not get multiplied - I just cannot replace it back 😉

Yes, you're right, it might be because of our older version of QVS.

Re: Visibility_condition for admins

Peter_Cammaert — Mon, 15 Dec 2014 13:40:09 GMT

I think so. Nevertheless, here are a few magical tricks that may save your day. Continuing where you left off with the !-replacement, these statements show you how to avoid expansion, and provide a quote to the replace() function. My only hope is that this does the same thing in your version of QV Desktop as it does in mine:

SET vString = OSUSER() = !DISNEY\mickey.mouse!;

LET vResult = REPLACE(vString, '!', chr(39)); // Works like a charm

Good luck,

Peter

Re: Visibility_condition for admins

datanibbler — Mon, 15 Dec 2014 13:48:16 GMT

A wonder 😉

I had tried the very same thing before - I have already noticed that those ASCII_functions can sometimes save your day - but to no avail. There must have been some typo somewhere.

now, however, it works.

Now I can have those two logons - hopefully more in the future - consolidated in one central file instead of hard-coding them everywhere and we won't have to reload the script, thereby losing all data that we don't have access to ourselves. That was the objective.

Thanks a lot!

Best regards,

DataNibbler