what is wrong with the resident load?

[Anonymous]

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.

[cgdonders]

Try placing the variable between single quotes, like this

Resident AllData

While MonthName(addmonths(today(),IterNo())) <= '$(vEndDate)'

[Anonymous]

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.

[aarkay29 · Accepted solution]

Try This

vEndDate = Num(addmonths(Monthstart(today()), 12));

load *,

MonthName(addmonths(today(),IterNo())) as MonthYear

Resident AllData

While Num(addmonths(Monthstart(today()),IterNo())) <= '$(vEndDate)';