Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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());
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.
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.
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
Thank you. It looks like the Temp table is not returning any rows. Do you know why that would be happening?
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());
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.