Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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());

View solution in original post

6 Replies
rubenmarin

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.

JonnyPoole
Employee
Employee

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.

Not applicable
Author


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

Not applicable
Author

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

JonnyPoole
Employee
Employee

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());

Not applicable
Author

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.