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

[marcoserilio]

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

[marcoserilio]

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';

[marcoserilio]

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 ?