Get expressions (or set analysis) from Excel

[#1]

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
1	sum({<account={">=3000<=3999"} >} amount)
2	sum({<account={">=4000<=4999"}>} amount)
3	sum({<account={5700}>} amount)

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

[#2]

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.

[Gysbert_Wassenaar]

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?

[#4]

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