topic Re: Loop load with month name in QlikView

Loop load with month name

Wed, 29 Oct 2014 20:55:36 GMT

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

Re: Loop load with month name

maxgro — Wed, 29 Oct 2014 21:03:38 GMT

for i=-1 to -12 step -1

DATES_BY_MONTH:

LOAD

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

RESIDENT MyTable;

next;

Re: Loop load with month name

MarcoWedel — Wed, 29 Oct 2014 23:32:49 GMT

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

Re: Loop load with month name

Thu, 30 Oct 2014 15:04:50 GMT

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;

Re: Loop load with month name

Thu, 30 Oct 2014 15:07:03 GMT

Grazie mille, Massimo Grossi!!

Perfect!

Pablo