topic Re: Load variables from file that contain variables in QlikView

Load variables from file that contain variables

cbushey1 — Tue, 24 Jul 2018 13:36:12 GMT

Hello there,

I already use the method outlined here by Barry Harmsen:

http://www.qlikfix.com/2011/09/21/storing-variables-outside-of-qlikview/

However, I have run into an issue where I have a need for a variable to be loaded based on the value of another variable. Let me explain what I mean.

Lets assume I have these two variables:

vMedClaim = 1;

vDrillNo = IF($(vMedClaim) = 1, '<250',250);

What I want to occur, during the script refresh, is for vDrillNo to either be '<250' or 250 as determined by the vMedClaim variable. However when I follow the method outlined in Barry's blog, the full expression is stored as the value of vDrillNo even though I am using the LET command.

Thoughts?

Re: Load variables from file that contain variables

sunny_talwar — Tue, 24 Jul 2018 13:38:36 GMT

Both vMedClaim and vDrillNo are part of the Excel file variables?

Re: Load variables from file that contain variables

cbushey1 — Tue, 24 Jul 2018 13:55:18 GMT

No.

vMedClaim comes from an include file that is loaded previously.

Re: Load variables from file that contain variables

ogautier62 — Tue, 24 Jul 2018 14:23:22 GMT

Hi,

example :

let v1 = 1;

let v2 = 'if($(v1)<5,''Z'',''W'')'; // here use twice simple quote

let v = $(v2);

so for you :

vDrillNo = $(IF($(vMedClaim) = 1, ''<250'',''250'')

$ if you want to interprete value in script,and two simple quote, if not blank

regards

Re: Load variables from file that contain variables

cbushey1 — Tue, 24 Jul 2018 16:47:19 GMT

Unfortunately this doesn't work for me.

I used single quotes around the values, (2 on each side as described).

Here is what my variable looks like once loaded into the application (the same as in excel).

Re: Load variables from file that contain variables

cbushey1 — Tue, 24 Jul 2018 19:25:54 GMT

Assume vMedClaim = 1

The desired stored value of vDrillNo = '<250'

Ultimately I am trying to use vDrillNo inside set analysis and it doesnt evaluate the way I would expect.

Re: Load variables from file that contain variables

sunny_talwar — Tue, 24 Jul 2018 19:28:53 GMT

Would you be able to create a mock up of what you have?

Re: Load variables from file that contain variables

ogautier62 — Wed, 25 Jul 2018 06:38:37 GMT

I thought you evaluate variable in script,

if it is in front :

in script :

let v1 = 1;

let v2 = 'if($(v1)<5,''Z'',''W'')';

don't put $ in definition of variable, but evaluate in front end

Re: Load variables from file that contain variables

cbushey1 — Wed, 25 Jul 2018 13:15:23 GMT

Yes that might be easiest.

Note - In these files the variables are vMedClaims and vDrilldownSvcCat (instead of vDrillNo).

My goal is for vDrilldownSvcCat to be loaded into qlikview having the expression evaluated (so it returns either the <250 or 250) as I am trying to use $(vDrilldownSvcCat) in the following set analysis:

=Sum({<Event_ServiceCat =$(vDrilldownSvcCat)>}Dollars)

TIA

Re: Load variables from file that contain variables

sunny_talwar — Wed, 25 Jul 2018 13:36:57 GMT

See if this works for you

=Sum({<Event_ServiceCat = {"$(vDrilldownSvcCat)"}>}Dollars)

Re: Load variables from file that contain variables

ogautier62 — Wed, 25 Jul 2018 13:39:02 GMT

so evaluate in script,

then in front end your variable is '<250'

Sum({<Event_ServiceCat ={"$(vDrilldownSvcCat)"}>}Dollars)

don't forget {" "}

example :

sum({<Montant = {"$(v)"} >} Montant)

Re: Load variables from file that contain variables

cbushey1 — Wed, 25 Jul 2018 14:46:32 GMT

Thanks for the continued effort here (from both of you). I really appreciate the assistance.

Sunny, adding the "=" to the variable in the excel file certainly resolves the issue, but I am concerned this will cause performance degradation as I have read that having the "=" in the variable causes it to be reevaluated with every click. I have a few variables to apply this kind of logic to.

Thoughts?

Re: Load variables from file that contain variables

sunny_talwar — Wed, 25 Jul 2018 14:53:12 GMT

I have not heard of that, but I barely use variables, so might have heard of it and then forgot about it.... anyways try now

variable

IF($(vMedClaims) = 1,'<250','250')

Expression

=Sum({<Event_ServiceCat = {"$(=$(vDrilldownSvcCat))"}>}Dollars)

Re: Load variables from file that contain variables

cbushey1 — Wed, 25 Jul 2018 15:17:58 GMT

Taking this approach came me some weird numbers for the dollars, but what I realized was I think I can get away with your previous suggestion (using the equal sign in the stored variable). As it turns out, I only need to store the equal sign for variables that are going to be used in set analysis as the other ones seem to evaluate the expression without issue.

I also didnt, need to use the double quotes around in the set analysis. It seemed to work either way, but is it best practice to use them?

Re: Load variables from file that contain variables

sunny_talwar — Wed, 25 Jul 2018 15:23:30 GMT

I also didnt, need to use the double quotes around in the set analysis. It seemed to work either way, but is it best practice to use them?

Yes, read about that here

Quotes in Set Analysis

Re: Load variables from file that contain variables

cbushey1 — Wed, 25 Jul 2018 15:47:30 GMT

Yes that is the exact post I was thinking of when I questioned the use so thanks for confirming. I will make sure to add them.

Re: Load variables from file that contain variables

cbushey1 — Wed, 25 Jul 2018 19:42:31 GMT

stalwar1‌

I have run into a snag using the double-quotes and it could just be how I am trying to combine everything.

Let me explain further.

Essentially this thread was to help me set a default value to vDrilldownSvcCat to avoid using sheet triggers. And that part works great and the charts all work as expected now (with the double-quotes). The issue comes in that I have several buttons that change the value assigned to vDrilldownSvcCat to just 250 or <250.

Once the value goes from the expression to just the <250 or 250 the double-quotes interfere with the set analysis and no results are displayed.

*Single quotes would work if I was only returning a literal but in the case of <250 I dont think single quotes will work.

Thoughts?

Re: Load variables from file that contain variables

sunny_talwar — Wed, 25 Jul 2018 19:52:19 GMT

So, are you saying that you have an over-arching double quotes within which you have another set of double quotes? The reason I ask this is because in the attached sample I made >250 and 250 both worked within the double quotes... but in your real scenario it isn't? What is the difference?

Re: Load variables from file that contain variables

cbushey1 — Wed, 25 Jul 2018 20:01:55 GMT

No that isnt the case.

As part of the variable load I am assigning a value to vDrilldownSvcCat to =If($(vMedClaims) = 1,'<250','250). So in the app the value is exactly as I outlined.

I then have buttons in the app that change the value of vDrilldownSvcCat to just 250 or just <250. Because in the application the user needs to be able to change the values but I needed to default the application into a certain value and that is the reason for the formula in the initial formula.

Re: Load variables from file that contain variables

sunny_talwar — Wed, 25 Jul 2018 22:22:16 GMT

Oh so, once you select the button to change the value, it stops working? Is that what the issue is?