Visibility_condition for admins

[datanibbler]

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

[Gysbert_Wassenaar · Accepted solution]

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

[datanibbler]

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

[Peter_Cammaert]

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.

[datanibbler]

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)));

[Peter_Cammaert]

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

[datanibbler]

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

[Peter_Cammaert]

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'

[Peter_Cammaert]

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.

[datanibbler]

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.