Can you use a nested variable when improrting variables from an Excel file

[helen_pip]

Hello

I have decided to load all my variables in from a spreadsheet as it was suggested as best practice

Most variables are working just fine through this technique, however the ones which are failing to return results are the variables which have variables within them

I.e.    

=Time(Fractile({$<FactDate = {">=$(=v_Start_Date_ED)<=$(=v_End_Date_ED)"}>}[Total Wait],0.95)/1440, 'hh:mm')

v_Start_Date_ED and v_End_Date_ED are also variables, and I was wondering why my entire variable was not working?

Could someone kindly let me know if you can refer to a variable within a variable when laoding in from a spreadsheet using the following code:

// Load Expressions to variable for user simplicity
// *** MOD This table was move to the User Interface load

Expressions:
LOAD Variable,
Expression,
Usage
FROM

(biff, embedded labels, table is [Sheet1$]);

Let vNumberOfRows = NoOfRows('Expressions');
For vI = 0 to (vNumberOfRows - 1)
Let vVariable_Name = Peek('Variable',vI,'Expression');
Let [$(vVariable_Name)] = Peek('Expression',vI,'Expression');
Next

Kind Regards

Helen

[Gysbert_Wassenaar]

I don't think that the nesting is the problem. Did you make sure the variables v_Start_Date_ED and v_End_Date_ED actually contain numeric date values by the time they're used in the expression?

[marcus_sommer]

I hope this is helpful for you:Re: Setting a variable in script...

- Marcus

[helen_pip]

Hello Marcus

Thank you for your response

I have read the article that you have kindly posted

What I want to do is load in my variables from an excel spreadsheet and refrain from doing any writing of the variables in the script

//Create my calculation variable
SET v_TotalWait_95thCentile = 'Time(Fractile({$<FactDate = {">=$(=v_Start_Date_ED)<=$(=v_End_Date_ED)"}>}[Total Wait],0.95)/1440, 'hh:mm')';

let v_TotalWait_95thCentile = replace('$(v_TotalWait_95thCentile)', '#', chr(36));

I am however not sure what I want to replace? Are you able to help as to what I should be replacing?

Gysbert mentioned that I should think about the date format, so I made the following change:

=Time(Fractile({$<FactDate = {">=$(=date(v_Start_Date_ED),'DD/MM/YYYY')<=$(=Date(v_End_Date_ED),'DD/MM/YYYY')"}>}[TotalWait],0.95)/1440, 'hh:mm')

But this does not seem to be able to interact with my master calender

Kind Regards

Helen

[helen_pip]

Hello Gysbert

Thank you for your reply

I have changed the formula in my spreasdsheet to be

v_Start_Date_ED = Date(Min({<data_source={'Main ED'}>}FactDate),'DD/MM/YYYY')

My expression which is looking at the v_Start_Date_ED is:

Time(Fractile({$<FactDate = {">=$(=date(v_Start_Date_ED),'DD/MM/YYYY')<=$(=Date(v_End_Date_ED),'DD/MM/YYYY')"}>}[Total Wait],0.95)/1440, 'hh:mm')

The only problem is that when v_Start_Date_ED loads in, it actually comes in as 41356 instead of 25/03/2014 etc

My Time(Fractile statement then fails to work with the master calender, could you kindly provide any advice on how I can get my two expressions to interact with each other?

[jonathandienst]

Hi

This will replace the $'s to prevent Qv attempting to expand the $(=..):

Expressions:

LOAD Variable,

  Replace(Expression, '$', '#') As Expression,

  Usage

FROM

(biff, embedded labels, table is [Sheet1$]);

Let vNumberOfRows = NoOfRows('Expressions');

For vI = 0 to (vNumberOfRows - 1)

  Let vVariable_Name = Peek('Variable',vI,'Expression');

  Let [$(vVariable_Name)] = Replace(Peek('Expression',vI,'Expression'), '#', '$');

Next

This is similar to Marcus' post in the other thread and what I have used for the same problem.

HTH

Jonathan

[marcus_sommer · Accepted solution]

Hello Helen,

I haven't read your post carefully enough. Through your extern variable-load and allocation per peak() you haven't an issue with the $-sign and did'nt needed to replace anything. I'm not sure yet

I suggest you to load your FactDate twice, one as your Key to other tables and one as num(FactDate) as FactDateNum for calculation needs. Your formulas without formatings inside will be easier, less error-prone and have more performance.

- Marcus

[Peter_Cammaert]

Has anybody seen the odd position (twice) of the Date format 'DD/MM/YYYY' in Helen's expressions? It surely isn't a second parameter value to the Date() function. Then what is it?

[helen_pip]

Hello Peter

I am just doing some testing on what has been ever so kindly suggested

I am currently loading in a FactDateNum field from my fact table and then ensure my variables and calender point to the FactDateNum field

This may take 30 minutes or so test to see if it works

Kind Regards

[Peter_Cammaert]

Then I may be too late. I was talking only about this part of your expression:

...>=$(=date(v_Start_Date_ED),'DD/MM/YYYY')<=...

Shoudn't the date format be inside the date() call? Now it's outside of the parentheses...