Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alenb
Partner
Partner

Using a OSUser() in expressions in load script

In my load script I define a variable with the following expression. 

Set vCurrentUserId = Upper(SubField(OSUser(), '=', -1));

I can then display user in Text object like so:

$(vCurrentUserId)

And it works fine.

But I want to use this in my other variables that are defining set expressions.

1. This works when defined in chart expression and in load script, of course

//chart

sum({ $< BillingYear={'2020'} UserID={'FRED'}> } Units);

//load

Set  mySum = sum({ $< BillingYear={'2020'} UserID={'FRED'}> } Units);

 

2. These work only when defined in chart expression but not in load script

//chart: working

sum({ $< BillingYear = {'2020'}, UserID={'$(=Upper(SubField(OSUser(), '=', -1)))'}> } Units);

//load: not working

Set  mySum = sum({ $< BillingYear = {'2020'}, UserID={'$(=Upper(SubField(OSUser(), '=', -1)))'}> } Units);


//chart: working

sum({ $< BillingYear={'2020'} UserID={"$(=Upper(SubField(OSUser(), '=', -1)))"}> } Units);

//load: not working

Set  mySum = sum({ $< BillingYear={'2020'} UserID={"$(=Upper(SubField(OSUser(), '=', -1)))"}> } Units);

 

3. These attempts don't work neither in chart expressions nor in load script.

Set  mySum = sum({ $< BillingYear={'2020'} UserID={$(vCurrentUser)}> } Units); // Error

Set  mySum = sum({ $< BillingYear={'2020'} UserID={"$(vCurrentUser)"}> } Units); // Returns 0

 

 

I guess there must be a way to A) Use the OSUser() inside the expressions, and B) predefine a variable that extracts the required part of the string from the OSUser(). 

 

 

 

 

 

 

 

 

 

 

 

2 Solutions

Accepted Solutions
rwunderlich

I believe your problem is because of the use of "$()" in your SET value. Script interpretation will always perform Dollar Sign Expansion -- substitution -- when encountered. So the "Upper(SubField(OSUser(), '=', -1)" expression is getting evaluated during the script run, instead of in the chart. If you look at your variable in the variable editor you'll see that the $() has already been replaced. 

There are a number of ways to work around the $( problem.  But first, if you are ok with coding the OsUser() function in the variable expression, you don't need the $ at all. You can write it:

SET  mySum = sum({ $< BillingYear = {'2020'}, UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}> } Units);

If you need to use $(, like for example you want to reuse vCurrentUser, you have several choices. I'm going to use some code close to yours because I have it laying around. You can adapt to your expression. 

First the problem.

SET vCurrentUser = =SubField(OSUser(), '\', -1);
SET e.Sales = Sum({<User={"$(=$(vCurrentUser))"}>}Sales); 

e.Sales won't work because the $(= will get evaluated during the script run. If I look at the variable value in the Variable Editor I see:

Sum({<User={""}>}Sales)

Alternative 1 -- Use LET to break the "$" and "(" apart and concatenate back together. 

LET e.Sales = 'Sum({<User={"$' & '(=$(vCurrentUser))"}>}Sales)'; 

Alternative 2 -- Use @ as a placeholder for $ and fixup with Replace(). 

LET e.Sales = replace('Sum({<User={"@(=@(vCurrentUser))"}>}Sales)', '@', '$'); 

Other alternatives include using MapSubstring() instead of Replace(), and loading the Variables from an external file. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

 

 

View solution in original post

alenb
Partner
Partner
Author

Lo and behold, this actually works. I'd never thought this would work: 

UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}

 

Does this mean that when {"= is used, what follows replaces what is before ={, but only in load script, and in chart script it is slightly different? 

 

 

As for the reusability, I ended up doing this:

Set vUIDExpression = UserID={"=UserID=Upper(SubField(OSUser(), '=', -1))"};

Set setLastMonth = BillingMonth = {$(PreviousMonth)}, BillingYear = {$(CurrentYear)}, $(vUIDExpression);

Set expLastMonth = sum({ $< $(setLastMonth ) > } Units);

Set expLastMonthSales = sum({ $< $(setLastMonth ) > } Sales);

// etc... 

All of it works now by being declared only load script, except the final expLastMonthSales is being used in chart expressions and visualizations. 

 

 

View solution in original post

5 Replies
joaopaulo_delco
Partner
Partner

Hi @alenb 

 

As I understand it, you would like to filter the data according to the OS user. Why don't you use section access?

Help users find answers! Don't forget to mark a solution that worked for you!
alenb
Partner
Partner
Author

I already have a bunch of similar expressions that now need to have also the user condition added.

As for Section Access, maybe I could pre-filter the data there, but I'm not sure how to do that either.

But is it not possible to filter data "on the fly" in the expressions like I posted? It seems that in some cases it does work and in others not.

rwunderlich

I believe your problem is because of the use of "$()" in your SET value. Script interpretation will always perform Dollar Sign Expansion -- substitution -- when encountered. So the "Upper(SubField(OSUser(), '=', -1)" expression is getting evaluated during the script run, instead of in the chart. If you look at your variable in the variable editor you'll see that the $() has already been replaced. 

There are a number of ways to work around the $( problem.  But first, if you are ok with coding the OsUser() function in the variable expression, you don't need the $ at all. You can write it:

SET  mySum = sum({ $< BillingYear = {'2020'}, UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}> } Units);

If you need to use $(, like for example you want to reuse vCurrentUser, you have several choices. I'm going to use some code close to yours because I have it laying around. You can adapt to your expression. 

First the problem.

SET vCurrentUser = =SubField(OSUser(), '\', -1);
SET e.Sales = Sum({<User={"$(=$(vCurrentUser))"}>}Sales); 

e.Sales won't work because the $(= will get evaluated during the script run. If I look at the variable value in the Variable Editor I see:

Sum({<User={""}>}Sales)

Alternative 1 -- Use LET to break the "$" and "(" apart and concatenate back together. 

LET e.Sales = 'Sum({<User={"$' & '(=$(vCurrentUser))"}>}Sales)'; 

Alternative 2 -- Use @ as a placeholder for $ and fixup with Replace(). 

LET e.Sales = replace('Sum({<User={"@(=@(vCurrentUser))"}>}Sales)', '@', '$'); 

Other alternatives include using MapSubstring() instead of Replace(), and loading the Variables from an external file. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

 

 

View solution in original post

alenb
Partner
Partner
Author

Lo and behold, this actually works. I'd never thought this would work: 

UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}

 

Does this mean that when {"= is used, what follows replaces what is before ={, but only in load script, and in chart script it is slightly different? 

 

 

As for the reusability, I ended up doing this:

Set vUIDExpression = UserID={"=UserID=Upper(SubField(OSUser(), '=', -1))"};

Set setLastMonth = BillingMonth = {$(PreviousMonth)}, BillingYear = {$(CurrentYear)}, $(vUIDExpression);

Set expLastMonth = sum({ $< $(setLastMonth ) > } Units);

Set expLastMonthSales = sum({ $< $(setLastMonth ) > } Sales);

// etc... 

All of it works now by being declared only load script, except the final expLastMonthSales is being used in chart expressions and visualizations. 

 

 

View solution in original post

rwunderlich

UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}

None of this is evaluated in the script. It is only stored in the variable for later use by the chart. This is the "Advanced Search" syntax.  The "=" after " indicates that instead of literal text, what follows is an expression that needs to be evaluated to or false.  So the diagram looks roughly like this:

UserID = { True | False}

Rows will included where the expression is True.

-Rob