topic Re: Qlik Sense Integration in Integration, Extension & APIs

Qlik Sense Integration

Tue, 01 Dec 2015 20:02:20 GMT

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?

Re: Qlik Sense Integration

JonnyPoole — Tue, 01 Dec 2015 21:59:16 GMT

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.

Re: Qlik Sense Integration

rwunderlich — Wed, 02 Dec 2015 03:01:48 GMT

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

Re: Qlik Sense Integration

JonnyPoole — Wed, 02 Dec 2015 03:10:58 GMT

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

Re: Qlik Sense Integration

Wed, 02 Dec 2015 18:05:56 GMT

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.

Re: Qlik Sense Integration

JonnyPoole — Wed, 02 Dec 2015 18:20:17 GMT

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 ?

Re: Qlik Sense Integration

Wed, 02 Dec 2015 19:44:03 GMT

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.

Re: Qlik Sense Integration

Wed, 02 Dec 2015 20:21:24 GMT

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!

Re: Qlik Sense Integration

JonnyPoole — Wed, 02 Dec 2015 20:38:33 GMT

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‌

Re: Qlik Sense Integration

Wed, 02 Dec 2015 21:42:37 GMT

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

Re: Qlik Sense Integration

rwunderlich — Thu, 03 Dec 2015 07:48:25 GMT

‌There is a discussion regarding creating master items from script here

New IDEA: Create master items (dimensions, measures) in script

Re: Qlik Sense Integration

Thu, 03 Dec 2015 17:10:11 GMT

Thanks Rob, I did check the post and downloaded FastTrack app. It works using an XML on desktop. Checking further on how to implement it on the server. Great help!

Re: Qlik Sense Integration

rwunderlich — Fri, 04 Dec 2015 04:36:28 GMT

‌Let us know how you fare implementing it on the server. I'm curious.

Re: Qlik Sense Integration

Fri, 18 Dec 2015 16:42:01 GMT

Hi Rob,

Just wanted to let you know that I haven't reached any further on the server side implementation yet. I need to try Architeqt but haven't don't that. Please let me know if you have any luck with serverside master items creation.