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: 
Anonymous
Not applicable

what is wrong with the resident load?

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.

1 Solution

Accepted Solutions
aarkay29
Specialist
Specialist

Try This


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

load *,

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

Resident AllData

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

View solution in original post

3 Replies
cgdonders
Partner - Creator
Partner - Creator

Try placing the variable between single quotes, like this

Resident AllData

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

Anonymous
Not applicable
Author

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
Specialist
Specialist

Try This


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

load *,

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

Resident AllData

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