topic Re: Variable upload from excel in App Development

Variable upload from excel

Nikita_Ben08 — Wed, 17 Sep 2025 07:31:54 GMT

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

Re: Variable upload from excel

FedericoDellAcqua — Wed, 17 Sep 2025 08:25:21 GMT

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!

Re: Variable upload from excel

Nikita_Ben08 — Wed, 17 Sep 2025 08:58:54 GMT

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');

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

Re: Variable upload from excel

Lech_Miszkiewicz — Wed, 17 Sep 2025 08:59:53 GMT

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

Re: Variable upload from excel

Nikita_Ben08 — Wed, 17 Sep 2025 10:19:19 GMT

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

Re: Variable upload from excel

Lech_Miszkiewicz — Wed, 17 Sep 2025 11:12:15 GMT

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

Re: Variable upload from excel

Nikita_Ben08 — Wed, 17 Sep 2025 12:39:30 GMT

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

Re: Variable upload from excel

Nikita_Ben08 — Wed, 17 Sep 2025 14:05:21 GMT

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,

Re: Variable upload from excel

Lech_Miszkiewicz — Wed, 17 Sep 2025 23:18:29 GMT

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-and-subscriptions-in/td-p/2495563

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

cheers