
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For loop with date range
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.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
