Qlik Sense Integration

[#1]

Hi,

Is it possible to store qlik expressions/variables in an excel or some other external storage and load it to Qlik Sense on demand via API, Webservice etc?

[JonnyPoole]

You can do it without the API using the data load editor.

Variables:

Load * inline [

MeasureName,MeasureExpression

Sales, Sum(Sales)

Costs,Sum(Costs)

];

set vSales=peek('Variables',0,'MeasureExpression')

set vCosts=peek('Variables',1,'MeasureExpression')

The 2nd argument is the row number of the table starting at 0. Set will not evaluate the condition , it will set the variable to the text. Use $()  expansion syntax to leverage the variable in an expression dynamically.

This approach is the CRUX of externalizing expressions and measures outside of QlikView and Qlik Sense for use in multiple applications. Its a key way to establish some data governance and inject new expressions into an existing production app.  Its a good technique to master !

ps:  you can do this with master items yet.  That would need the API.

[rwunderlich]

Jonathan, I think you meant to use "LET"?

[JonnyPoole]

Yes! .. definitely psyched myself out a little with the layers of expression resolution. Thank you Rob. 

[#5]

Hi Jonathan,

Thanks for your response. Much appreciated. Sorry if I didn't specify my requirements clearly. My requirement is store all the expressions for my measures in an external storage and publish that to Qlik instance from outside thru an API or Webservice. For Example say we have a measure by name Revenue

Name	Expression	Comment
Revenue	=sum(Price * Amount)	Total revenue

I want to store the expression logic in an excel or an external source and load that to Qlik on demand.

I'm new to Qlik so excuse me if i'm not using the right terminologies or not explaining it to the point. Let me know if you have any questions.

[JonnyPoole]

Sorry if you didn't understand.

You can do external storage in excel and import the expressions from excel into one or more Qlik apps using the data load editor. With this workflow the expressions are stored in Qlik as variables. Variables can be leveraged in every expression in Qlik Sense.  You could technically do this through the API but i see no need, its a tried and tested approach that works without the API. The sample above is extremely basic , but you could easily add loops to inject as many expressions as you want.  You can also do an 'incremental load' strategy to import expressions seperately to your regular data loads.

Where you DO need the API is if you want to store the expressions as master items (instead of variables). We cannot import external expressions into dimension/measure master items through the data load editor. Is that what you are trying to do ?

[#7]

Thanks again for the clarification. I understand your point now but yes, I also need to import the dim/measure master items thru this process. 

[#8]

Quick Update:

I was able to create a variable and load that into Qlik. Used that variable in my expression and got the right value. I now want to import external expressions into measure master item if that is possible thru APIs etc. which is my end goal. Again, I really appreciate your help on this. Thank you!

[JonnyPoole]

Thats great to hear !   You may want to leverage this for variables only because master items cannot yet be referenced in expressions. With master items, they must form the entire expression whether its a measure or dimension. But variables can be leveraged within existing expressions so they are a little more flexible for some situations.

For master items yes you need the API but... i don't have an example for you .

I'll make some inquiries however.  msi‌

[#10]

Thanks Jonathan. Please let me know if you hear anything regd API approach. Cheers...