topic Re: How to use a Variable in load editor to create a new variable in App Development

How to use a Variable in load editor to create a new variable

marcoserilio — Wed, 14 Mar 2018 15:49:24 GMT

Hi Everybody,

I'm struggling trying to make it work.

I've got this variable which define Current fiscal year into a load editor script:

load*;

SQL(select left(as400.[dbo].[CurrentFinancialyear](),4)CurrFiscalYear );

Let exp_CurrFiscal = 'CurrFiscalYear';

Now what I wanna do is to reuse exp_CurrFiscal value to define a further variable:

let exp_ActSales='sum({<FiscalYear={"2017"}>} WholesaleQuantity)';

I want to replace the "2017" filter value with exp_CurrFiscal variable, to make the exp_ActSales variable dynamically build up.

I tried something like:

let exp_ActSales='sum({<FiscalYear={$(exp_ActSales)}>} WholesaleQuantity)';

It didn't work obviously, I wouldn't be here writing differently.

Anyone who can help me out?

Thanks in advance

Marco

Re: How to use a Variable in load editor to create a new variable

marcoserilio — Thu, 15 Mar 2018 09:14:11 GMT

Strangely if i let the exp_CurrFiscal variable like this

let exp_CurrFiscal = if($(vMonth_Current)>=1 and $(vMonth_Current)<4,$(vYear_Current),$(vYear_Previous));

and then i reuse it s value afterwards into the next variable like this:

let exp_BpSales = 'sum({<FiscalYear={"'& $(exp_CurrFiscal)&'"}>} BPSales)

I can make it work, but if i let exp_CurrFiscal by getting the current fiscal year out from a user defined function in sql server, i cannot make it work..

here's the sql statement and variable function script:

LIB CONNECT TO 'kmit-srv2';

load*;

SQL(select cast(left(as400.[dbo].[CurrentFinancialyear](),4 )as int)CurrFiscalYear );

let exp_CurrFiscal = 'CurrFiscalYear';

Re: How to use a Variable in load editor to create a new variable

marcoserilio — Fri, 16 Mar 2018 08:06:39 GMT

Well my feeling it's there's no way to make qlik reuse during loading action a variable previously set via a sql statement.

I made some extra testing, as I'm sure that the variable is correctly build up with the sql statement I've been using.

In fact the exp_CurrFiscal variable :

load*;

SQL(select left(as400.[dbo].[CurrentFinancialyear](),4)CurrFiscalYear );

Let exp_CurrFiscal = 'CurrFiscalYear';

It's correctly build up when I put it on a report, but it's not during loading script action, it's as it was null

Most likely qlik cannot validate a variable set via a sql statement.

I'm assuming that unless qlik an exception is thrown back from sql server, the variable is considered as correct, but that the value it's only set at the very end of the loading process to be used on the reporting stage.

Can anyone confirm my understanding or if I'm wrong tell me how to make qlik validate and then reuse during a further script action the exp_CurrFiscal variable ?