Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings,
I have a fact table called AllData that I load. then I'm trying to loop through the next 12 months to populate a MonthData table. however, there's something wrong with my statement. what am I doing wrong?
...
MonthName(addmonths(today(),IterNo())) as MonthYear
Resident AllData
While MonthName(addmonths(today(),IterNo())) <= $(vEndDate);
I define vEndDate at the beginning of the script using MonthName(addmonths(today(), 12)). it's Feb 2018.
Try This
vEndDate = Num(addmonths(Monthstart(today()), 12));
load *,
MonthName(addmonths(today(),IterNo())) as MonthYear
Resident AllData
While Num(addmonths(Monthstart(today()),IterNo())) <= '$(vEndDate)';
Try placing the variable between single quotes, like this
Resident AllData
While MonthName(addmonths(today(),IterNo())) <= '$(vEndDate)'
thank you. it's at least loading now. however, I thought that I would get 12 months worth of records duplicated. the fact table had 2311 records. using the below while statement, I thought that I would get 2311 times 12 records.. what is wrong with my while statement?
MonthData:
Load
*,
MonthName(addmonths(today(),IterNo()-1)) as MonthYear
Resident AllData
While MonthName(addmonths(today(),IterNo()-1)) <= '$(vEndDate)'
;
I define vEndDate at the beginning of the script using MonthName(addmonths(today(), 12)). it's Feb 2018.
Try This
vEndDate = Num(addmonths(Monthstart(today()), 12));
load *,
MonthName(addmonths(today(),IterNo())) as MonthYear
Resident AllData
While Num(addmonths(Monthstart(today()),IterNo())) <= '$(vEndDate)';