topic How to insert function as part of dinamic expression created by script ? in QlikView

How to insert function as part of dinamic expression created by script ?

Sun, 04 Sep 2011 06:02:30 GMT

Hi guys,

Need some help to solve the following:

I have a table (chart) with a Group as dimension.

I create expressions dinamically from script.

In one of my expressions, I have to use the function GetCurrentField(GroupName).

The expression works ok as a regular expression in the chart:

Sum(Invoices.LineTotalNIS)
- Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1, $(vCurrentField)> Orders.PurchasePriceNIS * Invoices.TQuant)

Where the variable vCurrentField= GetCurrentField(Display2)

In the Script, I define the following expression:

Sum(Invoices.LineTotalNIS)
- Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1, $(vCurrentField)> Orders.PurchasePriceNIS * Invoices.TQuant)

If I replace the variable by one of the dimension in the group it works.

I tried also:

Sum(Invoices.LineTotalNIS)
- Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1, $(=vCurrentField)> Orders.PurchasePriceNIS * Invoices.TQuant)

And

Sum(Invoices.LineTotalNIS)
- Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1, GetCurrentField(Display2)

Orders.PurchasePriceNIS * Invoices.TQuant)

How can I work it around?

Thanks in advance,

Aldo.

How to insert function as part of dinamic expression created by script ?

swuehl — Sun, 04 Sep 2011 13:43:35 GMT

Hi Aldo,

not sure if I understood what you want to achieve:

I assume you are not wanting to evaluate the expression in your script (i.e. calculating the sums), which I think is not possible (using GetCurrentField() in the script).

So you probably want to set variables to the expression and use the variables in charts expressions?

Could you give some more information about the context of the above lines (e.g. where and how you defined vCurrentField in your script)?

But maybe I just don't get what you are after.

Regards,

Stefan

How to insert function as part of dinamic expression created by script ?

Sun, 04 Sep 2011 14:24:36 GMT

Hi Stefan,

I am defining the expression in script, but using it in as Chart expression throw variable.

I am defining vCurrentField = GetCurrentField(GroupName) in GUI.

Thanks for taking the time to help,

Aldo.

How to insert function as part of dinamic expression created by script ?

swuehl — Sun, 04 Sep 2011 18:09:11 GMT

Aldo,

do you need to define this variable in the script or would it be possible to define the variable in the GUI?

How to insert function as part of dinamic expression created by script ?

Mon, 05 Sep 2011 09:06:15 GMT

I can define the variable both in script or GUI

Re: How to insert function as part of dinamic expression created by script ?

swuehl — Mon, 05 Sep 2011 10:31:42 GMT

Aldo,

please have a look at attached - simplified - example. I've put the variable in the GUI, reason is, because I think it's problematic to use the dollar sign expression function call GetCurrentField in the script like that. But if we could use variables set in the GUI, I think a solution to your problem is not far away.

Most of the time I struggle with correct variable evaluation calls.

In the demo, if you drill down into the bar chart, the current field is used by the expression the table chart. I think that's what you want, using a variable, right?

Hope this helps (or at least could serve for further discussions),

Stefan

How to insert function as part of dinamic expression created by script ?

Mon, 05 Sep 2011 10:47:16 GMT

Stefan,

It is a little more complicated than that.

i am trying to updload an example (qvw) but don't find how to (don't have any icon to upload documents).

Regarding the issue, I need to create the whole expression in the script.

Thanks,

Aldo.

How to insert function as part of dinamic expression created by script ?

swuehl — Mon, 05 Sep 2011 10:55:12 GMT

Please use the advanced editor, there you find an upload option.

Re: How to insert function as part of dinamic expression created by script ?

Mon, 05 Sep 2011 10:59:42 GMT

I have attached an example.

Please note that (in multibox):

Sales1 is a simple expression and works nice.

Sales2 and Sales3 are some of the ways I am trying to insert the function GetCurrentField(Grp1) into the expression.

Aldo.

Re: How to insert function as part of dinamic expression created by script ?

swuehl — Mon, 05 Sep 2011 11:57:35 GMT

Hi Aldo,

I think the problem is to guard the GetCurrentField() function in the script in a way that it will not be evaluated during load, but then afterwards in the GUI.

(That's why you probably used the variable etc.)

I fought with this some time, but no real answer yet.

What I think could work is putting the expressions as variables in the GUI, I created two new variables as examples. Then evaluate the variables in the chart. I noticed that you want the flexibility to select the variables from a dropdown using a table. So I added a index to the table in the script and used this index to build the variable name to be called dynamically in the tables expression.

So please check out the results for Sales1 and Sales2 in the last column of the table.

This is not exactely what your original approach is like, because I can't hold the expression variables in the script. But maybe it kind of solve your business problem nevertheless.

Maybe I or someone else find a better solution to guard the functions call in the script, I am not able to spend more time on this approach today (but if you might have questions about the "workaround", don't hesitate to ask).

Regards,

Stefan

Re: How to insert function as part of dinamic expression created by script ?

Mon, 05 Sep 2011 14:07:40 GMT

Thanks Stefan.

Re: How to insert function as part of dinamic expression created by script ?

llauses243 — Wed, 07 Sep 2011 22:23:10 GMT

Hi,

For me the solution is posible from data with .txt no inline, pls to see image adjust

Good luck, Luis

How to insert function as part of dinamic expression created by script ?

swuehl — Wed, 07 Sep 2011 23:38:51 GMT

Luis, that's neat!

Haven't thought about that the inline load was causing the problem, thanks!

Stefan