Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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';
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 ?