topic Re: Why does a variable work sometimes but not all the time? in QlikView

Why does a variable work sometimes but not all the time?

Wed, 22 Oct 2014 14:56:58 GMT

This seems to be a strange problem. I have a variable that works or populates a value sometimes but other times it does not when I reload. It seems random.

Here is my code to declare the variable:

---------------------------------------------------------------------

Temp:

load

max(Year) as CFY

Resident MasterCalendar

where DateID=today();

LET vCurrentFY = peek('CFY',0,'Temp');

drop table Temp;

---------------------------------------------------------------------

The vCurrentFY ties the correct year to each product in the data model:

------------------------------------------------------------------------------------------------------------------------

FY as Product_Year,

if(FY='CY', ProductID&'_'&($(vCurrentFY)),

if(FY='LY', ProductID&'_'&($(vCurrentFY)-1),

if(FY='2LY', ProductID&'_'&($(vCurrentFY)-2)))) as Product_Year_Key

-------------------------------------------------------------------------------------------------------------------------

Has anyone else experienced something like this? If so, can you help me correct it?

Re: Why does a variable work sometimes but not all the time?

rubenmarin — Wed, 22 Oct 2014 15:04:12 GMT

I would check the log to verify Temp table has returned any rows, and if it does, check when $(vCurrentFY) is empty in the rest of the log (seaching for "ProductID&'_'&()") to retrieve any tips on what is happening.

Re: Why does a variable work sometimes but not all the time?

JonnyPoole — Wed, 22 Oct 2014 15:12:14 GMT

Agree with Ruben, consider passing your table name 'temp' to this handy table function to see if there are any rows returned from the inital data load from the master calendar

NoOfRows('Temp')

If there are 0, then you may need to set your variable differently.

Re: Why does a variable work sometimes but not all the time?

Wed, 22 Oct 2014 15:24:54 GMT

Hi,

Try like this

FY as Product_Year,

if(FY='CY', ProductID&'_'&($(=vCurrentFY)),

if(FY='LY', ProductID&'_'&($(=vCurrentFY)-1),

if(FY='2LY', ProductID&'_'&($(=vCurrentFY)-2)))) as Product_Year_Key

Re: Why does a variable work sometimes but not all the time?

Wed, 22 Oct 2014 17:18:14 GMT

Thank you. It looks like the Temp table is not returning any rows. Do you know why that would be happening?

Re: Why does a variable work sometimes but not all the time?

JonnyPoole — Wed, 22 Oct 2014 17:30:17 GMT

probably because you sometimes have no records where DateID=today();

your code seems to calculating the year of today

how about just doing this and save the trouble ?

LET vCurrentFY = Year(Today());

Re: Why does a variable work sometimes but not all the time?

Wed, 22 Oct 2014 17:41:15 GMT

Perfect! that seems to have worked. I just removed the Temp Table all together and just declared my variable as you stated. After the reload everything looks good.

Thank you.