Using IterNo() to loop resident table

[#1]

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.

[kaushiknsolanki]

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

[#3]

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.

[#4]

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

[#5]

For ex, the output has these records

12/04/2012	13/04/2012	Rolling
11/04/2012	12/04/2012	Rolling
11/04/2012	13/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.

[#6]

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.