2 Replies Latest reply: Mar 19, 2018 11:04 AM by Leonardo Contezini RSS

    For loop with date range

    Clarence Peter

      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.

        • Re: For loop with date range
          Marcus Sommer

          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

          • Re: For loop with date range
            Leonardo Contezini

            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