Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

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
Highlighted
Valued Contributor

Re: what is wrong with the resident load?

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

Re: what is wrong with the resident load?

Try placing the variable between single quotes, like this

Resident AllData

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

Highlighted
Contributor

Re: what is wrong with the resident load?

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.

Highlighted
Valued Contributor

Re: what is wrong with the resident load?

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