topic Re: Outsourcing a formula in QlikView

Outsourcing a formula

datanibbler — Fri, 19 Jun 2015 09:04:27 GMT

Hi,

I want to outsource a formula (put it in a variable and define that in a qvd to be loaded when the script runs).

That way the formula will be available to several apps without any risk of typos and such.

The formula as such works, I had it in the app itself before trying to outsource it.

The issue seems to be, I have a MATCH() function inside that formula with a matchstring that I have built as another variable,

which has a number of single quotes (around the individual elements).

<=> As I also need single quotes around the entire formula (and use LET to define it) this causes confusion

=> So I tried putting chr(39) around it all which is just the same

<=> Right now, the INCLUDE command I have put in the script to load the txt file that contains the definition of the formula returns an empty string inside single quotes.

Could it be that all the data has to be available before I load this variable? I don't think so, but the error must be somewhere ...

Here is the formula definition I currently have:

>> LET e_formula_VPTA = chr(39) &

IF(MATCH(ITEM_MASTER.COMMENT_CODE, $(v_LVG_matchstring)), 9,

IF(MATCH(ITEM_MASTER.COMMENT_CODE, $(v_LVP_matchstring)), 6,

IF(ITEM_PACKTIMES.TYPE='I',(ITEM_PACKTIMES.SALES_PACKTIME*PACKAGE_ITEMS.ORD_QTY) +ITEM_PACKTIMES.SALES_PROCTIME,

ITEM_PACKTIMES.SALES_PACKTIME+ITEM_PACKTIMES.SALES_PROCTIME)))

& chr(39)

;

Can anyone spot the error here?

Thanks a lot!

Best regards,

DataNibbler

Re: Outsourcing a formula

Gysbert_Wassenaar — Fri, 19 Jun 2015 09:16:04 GMT

Try this:

LET e_formula_VPTA =

'IF(MATCH(ITEM_MASTER.COMMENT_CODE, $' & '(v_LVG_matchstring)), 9,

IF(MATCH(ITEM_MASTER.COMMENT_CODE, $' & '(v_LVP_matchstring)), 6,

IF(ITEM_PACKTIMES.TYPE=''I'',(ITEM_PACKTIMES.SALES_PACKTIME*PACKAGE_ITEMS.ORD_QTY)

+ITEM_PACKTIMES.SALES_PROCTIME,

ITEM_PACKTIMES.SALES_PACKTIME+ITEM_PACKTIMES.SALES_PROCTIME)))'

;

Re: Outsourcing a formula

jonathandienst — Fri, 19 Jun 2015 09:18:51 GMT

The way I see it, you have two problems to solve - first the quotes, and second, that you need to defer the $ expansions so that they happen when you use the expression in the front end, and not here in the Let/Set statement. So, if I have understood you correctly you will need to do something like this:

Set e_formula_VPTA =

'IF(MATCH(ITEM_MASTER.COMMENT_CODE, #(v_LVG_matchstring)), 9,

IF(MATCH(ITEM_MASTER.COMMENT_CODE, #(v_LVP_matchstring)), 6,

IF(ITEM_PACKTIMES.TYPE=''I'',(ITEM_PACKTIMES.SALES_PACKTIME*PACKAGE_ITEMS.ORD_QTY) +ITEM_PACKTIMES.SALES_PROCTIME,

ITEM_PACKTIMES.SALES_PACKTIME+ITEM_PACKTIMES.SALES_PROCTIME)))'

;

Let e_formula_VPTA = Replace(e_formula_VPTA, '#', '$');

The '' embedded in the Set string will produce a single quote. The $ expansion is deferred by using # instead of $ and then replacing with a $ in the Let statement.

Re: Outsourcing a formula

datanibbler — Fri, 19 Jun 2015 09:32:30 GMT

Hy Gysbert,

this seems to work fine. Thank you!