topic Get expressions (or set analysis) from Excel in QlikView

Get expressions (or set analysis) from Excel

Tue, 01 Oct 2013 09:25:39 GMT

Hi!

I'm trying to load alot of expressions into qlikview using Excel but I can't use them since QlikView does not recognize them as expressions.

I read this guide:

http://www.qlikfix.com/2011/09/21/storing-variables-outside-of-qlikview/

But this doesn't really help me since I have about 30 company IDs which have different expressions for the same KPI.

So my Excel looks a bit like this:

CompanyID

Expression

sum({<account={">=3000<=3999"} >} amount)

sum({<account={">=4000<=4999"}>} amount)

sum({<account={5700}>} amount)

Is there any easy way to import these whole expressions or just the set analysis and use in Qlikview?

Re: Get expressions (or set analysis) from Excel

Tue, 01 Oct 2013 16:43:55 GMT

I do something similar using Excel. Here's how I do it:

Add a tab to your report script to load the expressions into a table. You can use the Field Data.. File Wizard to help you select/format the input. It'll end up looking something like:

ExcelTable:

Load num(%CompanyID,'##0') as %CompanyID,

purgechar(%XLExpression,'"') as %XLExpression

FROM

[.\DataSources\QVD\expression_sheet.xlsx]

(ooxml, embedded labels, header is .... etc...

Note the use of purgechar because my expressions all begin with equals (=) and Excel requires expressions that start with equals to be enclosed in quotes (") otherwise it tries to execute them.

Now in your report, you can use the loaded expressions in your chart expressions. For example:

sum({<$(=%XLExpression)>} Sales)

Of course this particular form requires a selection in the either the %CompanyID or %XLExpression fields.

Re: Get expressions (or set analysis) from Excel

Gysbert_Wassenaar — Tue, 01 Oct 2013 16:46:15 GMT

Importing the expressions into a field is trivial. But you won't be able to use companyid as dimension and the value of a field as expression. You'll have to use the pick function. See Expressions in Fields and AVG and SUM functions reside on the same expression?

Re: Get expressions (or set analysis) from Excel

Tue, 01 Oct 2013 17:05:19 GMT

I forgot to mention that in our particular case, we don't put the whole expression into Excel, only the set analysis part.