For loop with date range

[clarenceclay]

DATA:

LOAD

     count(if( [Type]='Trading' and '31/12/2014' <= [End Date] and [Start Date] <= '31/12/2014' ),[Company No])) as 2014,

     count(if( [Type]='Trading' and '31/12/2015' <= [End Date] and [Start Date] <= '31/12/2015' ),[Company No])) as 2015,

     count(if( [Type]='Trading' and '31/12/2016' <= [End Date] and [Start Date] <= '31/12/2016' ),[Company No])) as 2016,

     count(if( [Type]='Trading' and '31/12/2017' <= [End Date] and [Start Date] <= '31/12/2017' ),[Company No])) as 2017,

     count(if( [Type]='Trading' and '31/12/2018' <= [End Date] and [Start Date] <= '31/12/2018' ),[Company No])) as 2018,

RESIDENT

     MASTER;

Hi folks,

Let say i have load statement like above. How could i possible turn that into a for loop statement? I don't want to hard-code the date

Would like to use for loop to populate the date.

Or perhaps there is a better way of doing this.

Thank you.

[marcus_sommer · Accepted solution]

You could try something like this:

for each vYear in 2014,2015,2016,2017,2018

     let vTableStatement = if(noofrows('table')>0, 'join(table)', 'table:');

     $(vTableStatement)

     load Key, count([Company No]) as [$(vYear)]

     resident Master where [Type]='Trading' and year([End Date]) = $(vYear) and year([Start Date]) = $(vYear)

     group by Key;

next

Whereby if there isn't a special requirement to calculate with these data within the script I would probably not create such a crosstable else calculate them within the gui (and adjust the datamodel with an intervalmatch and/or a master-calendar if it's needed).

- Marcus

[lcontezini]

You will need another dimension in order to do that count during LOAD.

You're going to use something like this:

TABLE:

LOAD

null() as [dimension 1],

null() as [Year]

AutoGenerate(1);

for each YY in '-4', '-3', '-2', '-1', '0'

LET vYear = date(addyears(today()-1, $(YY)),'YYYY');

LET vMonthEnd = date(yearend(addyears(today()-1, $(YY))),'DD/MM/YYYY');

concatenate(TABLE)

LOAD

     [dimension 1],

     count([Company No]) as $(vYear)

RESIDENT MASTER

WHERE [Type] = 'Trading' and [End Date] >= $(vMonthEnd) and [Start Date] >= $(vMonthEnd)

GROUP BY [dimension 1];

next