Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have the following variable:
LET vPeriod = date(Makedate(YEAR,Month,1),'YYYYMM');
When I use one of these expression in my field date(Makedate(YEAR,Month,1),'YYYYMM') as Period, it works
Now I want to create the end period using the Let statement 'vPeriod'
Load
year,
month,
Date(monthstart(addmonths('$(vPeriod)',12)),'YYYYMM') as EndPeriode
From Database......
I don't get any error when reloading. However no data can be found in the EndPeriode.
What am I doing wrong?
iSam
Hi,
Did you checked without $()?like this
Load
year,
month,
Date(monthstart(addmonths(vPeriod,12)),'YYYYMM') as EndPeriode
From Database......
Celambarasan
Hi iSam,
Just do not warp the expansion using single quotes:
Date(monthstart(addmonths($(vPeriod),12)),'YYYYMM') as EndPeriode
Hope that helps.
Miguel
Hi Miguel,
If i do that, I get the following error:
Error in expression:
')' expected
My expression seems to be oke right?
date(monthstart(addmonths($(vPeriod),12)),'YYYYMM') as EndPeriod
Thanks for you help!
Isam
Hi,
If I remove the $ sign I get the error that the field cannot be found. Which makes sense
Thanks for your help so far.
Isam
Hi Aboumejjane,
Maybe this solution is not so smart, but I think it works:
LET vEndPeriod = Date(monthstart(addmonths(vPeriod,12)),'YYYYMM');
LOAD data,
Year,
Month,
if (1=1,$(vEndPeriod)) as EndPeriode
FROM ...
I hope it helps
Regards
Giampiero
Hi Isam,
You are using Date() function with a value that is not a date (the variable). According to your LET script above, the following works here, note the Date#() function:
Date(MonthStart(AddMonths(Date#($(vPeriod), 'YYYYMM'), 12)), 'YYYYMM')
Hope that helps.
Miguel
Hi Miguel,
Thanks for your help and sorry for my very late response!
I found the problem. I was using the date function twice, once in the in the variable and once in my expression.
It should have been Let vPeriod = Makedate(Year,Month,1);
Load
*,
Date(monthstart(addmonths('$(vPeriod)',12)),'YYYYMM') as EndPeriod
I haven't tried your solution yet, But I assume that that will work also.
Many thanks for your help!!!
iSam