5 Replies Latest reply: Apr 18, 2012 6:56 AM by Ravi Chandran RSS

    Using IterNo() to loop resident table

      Hi - Do you have any example of how to loop through and get what I need. My requirement is:

      I have a table already loaded with following fields

      Date                Value

      16/01/2012      10

      17/01/2012       203

      18/01/2012       495

      19/01/2012       494

      20/01/2012       493

      23/01/2012       494

      and so on (note that this table only has working days, not the holidays or weekends)

       

      I need to build a table so that it gets all these with current option and then the last 15 days (ie., records) with rolling period. For ex

       

      Date       AsOfDate       AsOfType

      16/01     16/01              Current

      16/01     16/01              Rolling

      17/01     17/01              Current

      17/01     16/01              Rolling

      17/01     17/01              Rolling

      18/01     18/01              Current

      so on , The Rolling should only go back to last 15 days, all should exist in the resident table (one above)

       

      I tried to build this using IterNo() and peek() does not seem to get what I need...

       

      AsOf:

      Load

      TransactionDate as AsOfDate,

      'Current' as AsOfType,

      TransactionDate

      Resident DailyRevenue;

      Concatenate (AsOf)

      load ....

      here I used IterNo() with while condition and peek() in the load statement but not really got what I need.

       

      Any help please.

        • Re: Using IterNo() to loop resident table
          Kaushik Solanki

          Hi,

           

             Try this script

           

           

          SET ThousandSep=',';

          SET DecimalSep='.';

          SET MoneyThousandSep=',';

          SET MoneyDecimalSep='.';

          SET MoneyFormat='$#,##0.00;($#,##0.00)';

          SET TimeFormat='h:mm:ss TT';

          SET DateFormat='M/D/YYYY';

          SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

          SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

          SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

           

           

          Let vMin = num(makedate(2011,01,01));

          Let vMax = num(MakeDate(2011,03,31));

           

          Data:

          Load date($(vMin) +RowNo() -1) as Date,

               ceil(Rand() * 1000 + 450) as Value,

               'Current' as AsOfDay

          AutoGenerate 1

          While date($(vMin) +RowNo() -1) < date($(vMax));

           

          Temp:

          Load max(Date) as MAX Resident Data;

           

          let vMaxDate = (peek('MAX',0,'Temp')-15);

           

          let vCount = FieldValueCount('Date');

           

          for i=0 to $(vCount) -1

           

          if num(peek('Date',$(i),'Data')) >= $(vMaxDate) then

           

             load peek('Date',$(i),'Data') as Date,peek('Value',$(i),'Data') as Value,'Rolling' as AsOfDay

          AutoGenerate 1;

           

          ENDIF

          next

           

           

           

          Have a look at the QVW too

           

          Regards,

          Kaushik Solanki

            • Using IterNo() to loop resident table

              Thanks for your quick reply, but this is not exactly what I was looking for. I need Date, AsOfDate and AsOfType as shown above. But I will try to work out but in the meantime if you have any logics which does then pl let me know.

               

                • Using IterNo() to loop resident table

                  Hi - I am nearly there, if you look at this example, it takes always the last 15 for rolling instead of to work out from the TransactionDate value. I think as per this code, this is wrong. How do I get the last 15 from the TransactionDate instead of always the last 15 for Rolling.

                   

                  Data:
                  Load TransactionDate,
                       TransactionDate as AsOfDate,
                       'Current' as AsOfType
                  Resident DailyRevenue;

                  Temp:
                  Load max(TransactionDate) as MAX Resident Data;

                  let vMaxDate = (peek('MAX',0,'Temp')-15);

                  let vCount = FieldValueCount('TransactionDate');

                  for i=0 to $(vCount) -1

                  if num(peek('TransactionDate',$(i),'Data')) >= $(vMaxDate) then


                  load TransactionDate,
                  peek('TransactionDate',$(i),'Data') as AsOfDate,
                  'Rolling' as AsOfType
                  Resident Data
                  order by TransactionDate desc;

                  ENDIF
                  next

                    • Using IterNo() to loop resident table

                      For ex, the output has these records

                       

                      12/04/2012 13/04/2012 Rolling
                      11/04/201212/04/2012 Rolling
                      11/04/201213/04/2012 Rolling

                      etc,

                       

                      these should not have in the final table, instead the last 15 dates for Rolling from 12/04 or 11/04 accordingly.

                        • Using IterNo() to loop resident table

                          Thanks kaushik. Managed to get it using the iterno and while loop only. Just in case if anyone has any issues in terms of how to do Rolling Average using load script instead of RangeAvg on the chart. As you know RangeAvg will only work with the data that you selected on the charts, not outside which might be a problem. Therefore it's better to do using the load script and then you can easily display the moving average on the chart even for the data that's not visible in the chart.

                           

                          A couple of steps:

                          a) Just reload your original data (going back to 90 days or 30 days) based on the requirement using IterNo() and While loop, ex

                           

                          Rev_90days:

                          load Value,

                          Team,

                          dayname(TransactionDate,IterNo()-1) as TransactionDate

                          Resident RevRawData

                          while dayname(dayname(TransactionDate,-90),IterNo()-1) <= TransactionDate;

                           

                          b) Secondly, just agreegate on this new table to get the Moving Average.

                           

                          Rev_90DaysAvg:

                          NoConcatenate

                          load TransactionDate,

                          Team,

                          sum(Value)/count(TransactionDate) as Rev_Avg

                          Resident Rev_90days

                          where TransactionDate <=  $(vMaxDate)

                          group by TransactionDate,

                          Team;

                           

                          Hope this helps if anyone needs to do similar thing in your project.