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

Loop load with month name

Hi all!

I have a load statement like

DATES_BY_MONTH:

LOAD:

MonthName( (monthend(addmonths (today(),-1,1))) ) as Date

RESIDENT MyTable

Concatenate

LOAD:

MonthName( (monthend(addmonths (today(),-2,1))) ) as Date

RESIDENT MyTable

Concatenate

LOAD:

MonthName( (monthend(addmonths (today(),-3,1))) ) as Date

RESIDENT MyTable

and so on...

I need to do this concatenation for each month name available in my date field on "MyTable". I would to set a loop that browse these months names and make the load dynamically. In the example I'm adding a negative number for each month name on the addmonths function.

Is this possible?

Thanx you all!!!

Pablo

1 Solution

Accepted Solutions
maxgro
MVP
MVP

for i=-1 to -12 step -1

     DATES_BY_MONTH:

     LOAD

     MonthName( (monthend(addmonths (today(),$(i),1))) ) as Date

     RESIDENT MyTable;

next;

View solution in original post

4 Replies
maxgro
MVP
MVP

for i=-1 to -12 step -1

     DATES_BY_MONTH:

     LOAD

     MonthName( (monthend(addmonths (today(),$(i),1))) ) as Date

     RESIDENT MyTable;

next;

MarcoWedel

Hi,

can you describe a bit more in detail what you like to achieve with this script?

Maybe with sample data for input as well as output.

One remark though:

MonthName( (monthend(addmonths (today(),-3,1))) ) as Date


should return no different results than


MonthName(AddMonths(Today(),-1)) as Date

as the MonthEnd function is rendered useless by the MonthName function as well as the third parameter of the AddMonths function has no effect due to the enclosing MonthEnd function.

hope this helps

regards

Marco

Not applicable
Author

Hi again Marco Wedel!

I want to obtain a snapshot table with a date field ("Date") not related with my date transactional field (CreationDate). I can do it well with the loop syntax provided by Massimo.

I think this will be perfect for now!

Thanx!!

for i=-1 to -3 step -1

TicketsSnapshot:

LOAD

MonthName( (monthend(addmonths (today(),$(i),1))) ) as Date,

// TICKETS CREATED ON PREVIOUS MONTH

Count (if(MonthEnd(CreationDate)= (monthend(addmonths (today(),$(i),1))), TicketNumber)) as Created,

// TICKETS ACCUMULATED UNTIL PREVIOUS MONTH

Count (if(MonthEnd(CreationDate)<= (monthend(addmonths (today(),$(i),1))),TicketNumber)) AS [Accumulated Tickets]

Resident TICKETS;

next;

Tickets.jpg

Not applicable
Author

Grazie mille, Massimo Grossi!!

Perfect!

Pablo