Variable upload from excel

[Nikita_Ben08]

I have one  excel with  this column how to load excel variable to Qliksense variable dummy excal

vBudget

sum({<year = {$(=max(Year))},ReportingMonth={$(=num(month(MonthEnd(max({<Flag_sales={1}>}PeriodNum)))))} ,metric = {'xtx'}>}Budget)/1000000000

vActual

sum({<year = {$(vMaxCalenderYear)},ReportingMonth={$(vMonthNum)} ,metric = {'abc'}>}Actual)/1000000000
vMaxYear, max(Year)

vMonthNum,

num(month(MonthEnd(max({<Flag_sales={1}>}PeriodNum))))

[FedericoDellAcqua]

Hello @Nikita_Ben08 , 

This is really tricky! I also had the same idea and came out whit this: 

You should organize your excel with a column named EXPRESSION_LABEL containing the name of your desired variable (ex vBudget) and a column EXPRESSION containing the variable expression. Load the excel into your application and call this table Expression.

The last point, you need to copy and run this script: 

For vs_RowNum = 0 to NoOfRows('Expressions')-1

Let vs_ExpressionLabel = Peek('EXPRESSION_LABEL', '$(vs_RowNum)', 'Expressions');

Let $(vs_ExpressionLabel) = Peek('EXPRESSION', '$(vs_RowNum)', 'Expressions');

Let vs_ExpressionLabel = Null();

Next vs_RowNum;

In this way, the for cicle will generate the variables indicated by your excel. 

Please feel free to leave a like to this comment or accept as a solution if it helps you out! 

[Nikita_Ben08]

Hi ,

Below is my sample excel and code 

[SETExp_Master]:

LOAD

[VariableName],

[VariableValue]

 FROM [lib://AttachedFiles/ Variable  Upload.xlsx]

(ooxml, embedded labels, table is sheet) Where Len(VariableName)>0;

FOR i = 0 to NoOfRows('SETExp_Master')-1

     LET $(peek('VariableName', i, 'VariableName')) = peek('VariableValue', i, 'VariableName');

NEXT

generated output from above code

=sum({<year = {"=max(Year)"},ReportingMonth={'=num(month(MonthEnd(max({<Flag_sales={1}>}PeriodNum))))'}, cte_metric = {'xtxi'}>}Budget)/1000000

VariableName	VariableValue
vBudget	sum({<year = {$(=max(Year))},ReportingMonth={$(=num(month(MonthEnd(max({<Flag_sales={1}>}PeriodNum)))))} ,metric = {'xtx'}>}Budget)/1000000000
vActual	sum({<year = {$(vMaxCalenderYear)},ReportingMonth={$(vMonthNum)} ,metric = {'abc'}>}Actual)/1000000000
vMaxYear	max(Year)
vMonthNum	num(month(MonthEnd(max({<Flag_sales={1}>}PeriodNum))))

[Lech_Miszkiewicz · Accepted solution]

Below is a standard SUB we use in most of our apps. I suggest you update your spreadsheet and then you can then load data from XLS files with structure as on picture below:

    /*  Application Variables loaded from XLS, to prevent loss due to data reduction / corruption etc
	Also enables Variables to be easily re-usable in other applications, and lookup variable values without opening app */

		TRACE ---------- Loading Application Variables ------------;

//	Sub used to allow Load of Variables from multiple sheets to allow readability in the Excel file ---
      
      	Sub LoadVariables(zSheet)
        
         	//	Load Workbook containing variable library, kept in external file due to need to keep single quote treatment simple ---
          	VariablesLoadTemp:
          	LOAD 
          		Variable, 
            	Definition
          	FROM 
            	$(vConnection)\XLS\Variable Library\$(vVarLibrary)$(vSuffix).xlsx
          		(ooxml, embedded labels, table is $(zSheet))
             ;

          //--- Convert Measure variable library into variables ---
          FOR i = 1 to NoOfRows('VariablesLoadTemp')
              LET zTempVar 		= peek( 'Variable', i - 1, 'VariablesLoadTemp' );
              LET $(zTempVar)	= Lookup('Definition','Variable','$(zTempVar)','VariablesLoadTemp');
          NEXT i

          //--- Clean Up ---
          DROP TABLE VariablesLoadTemp;
          LET i = NULL();
          LET zTempVar = NULL();

      END SUB

      CALL LoadVariables('Application');				//--- Library of Other application Variables
      CALL LoadVariables('Measures');					//--- Library of Measure Variables
      CALL LoadVariables('Colours');					//--- Library of Colours Variables

[Nikita_Ben08 · Accepted solution]

Wow thanks, your solution actually  worked .

One more question how can load below expresssion in excel. Its not able to capture '>=' after  excel upload it convert this to >='  (without front  single quotes)

='>='& Floor(MonthEnd(YearStart(vMaxDateNum))) & '<=' & vMaxDateNum

for now i convert above as this 

Chr(62)&Chr(61)&Floor(MonthEnd(YearStart(vMaxDateNum))) & '<=' & vMaxDateNum

[Lech_Miszkiewicz]

I am not sure if I understand correctly.

I am assuming your issue is that value in excel cannot start with = sign or > sign. Solution to this is to put one single quote which excel will treat rest of your cell value as text and not as formula, so why dont you just use this in excel (see the red single quote at the beginning)

'>= Floor(MonthEnd(YearStart(vMaxDateNum))) <= vMaxDateNum

[Nikita_Ben08]

Is there is any way in Qliksense to export variable .Like in Qlikview we can add all variable in Input box then send to excel. Is there is any similar way in Qliksense

[Nikita_Ben08]

I wanted to highlight an issue I am currently facing with Qlik Sense variables. Whenever I change the value of a variable or update a formula and then republish the app, the change is reflected for a short while, but after some time it automatically rolls back to the previous formula/value.
For example, if there is a variable ABC with the current value of 50, and I update it to 150 and republish it from Work stream to UAT stream , it initially shows the updated value of 150. However, after refresh/publish, it reverts back to the old value of 50.
I checked online and noticed that other users are also experiencing similar issues with variables after the latest Qlik Sense update.
Do we have any solution or workaround for this,

[Lech_Miszkiewicz]

Hi @Nikita_Ben08 

We are getting away from the topic of this thread... but shortly:

Is it possible that you have some variables values stored in Bookmarks. There was a period of time when storing variable values as part of bookmark was changing and here is and explanation:https://community.qlik.com/t5/Visualization-and-Usability/Upcoming-change-to-variables-in-bookmarks-...

That could be a reason why after applying say default bookmark you could have a behaviour when variable set was changed.

cheers