topic Re: Can you use a nested variable when improrting variables from an Excel file in QlikView

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

helen_pip — Fri, 07 Mar 2014 15:48:34 GMT

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

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

Gysbert_Wassenaar — Fri, 07 Mar 2014 15:54:10 GMT

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?

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

marcus_sommer — Fri, 07 Mar 2014 15:56:21 GMT

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

- Marcus

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

helen_pip — Mon, 10 Mar 2014 10:17:02 GMT

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

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

helen_pip — Mon, 10 Mar 2014 10:53:32 GMT

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?

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

jonathandienst — Mon, 10 Mar 2014 11:13:00 GMT

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'), '#', '$');

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

HTH

Jonathan

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

marcus_sommer — Mon, 10 Mar 2014 11:14:20 GMT

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

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

Peter_Cammaert — Mon, 10 Mar 2014 11:43:58 GMT

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?

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

helen_pip — Mon, 10 Mar 2014 11:50:38 GMT

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

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

Peter_Cammaert — Mon, 10 Mar 2014 12:03:54 GMT

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...

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

helen_pip — Mon, 10 Mar 2014 12:38:28 GMT

Hello Marcus

Thank you for your suggestion....I have got this working by adding FactDateNum field to my fact table and then by variables and master calender are working off the FactDateNum field

Thank you for all your help

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

helen_pip — Mon, 10 Mar 2014 12:50:57 GMT

Hello Peter

Thank you for your response

I have got my variable working by going back to my original sourse and adding a FacDateNum field. My variables all now included FactDateNum fields instead and it appears to be working O.K for me

I would like to thank you for your suggestions and advice

Kind Regards

Helen