Moving Fields 12 Months Max and Min

[mbespartochnyy]

Hello everyone,

I'm facing a challenge calculating a moving max and min values for a database with growing number of fields. Attached is the database that I'm working with and an app I've created so far.

My goal was to shrink the database from 70 some fields down to 24 by positioning all the fields next to Deal Date. However, after I've done that, it messed up calculations of min and max values for a season.

In attached spreadsheet, in Example tab, you'll see the correct min and max 1-year average values are 63.42 and 363.38. However, the application is showing min of 53.08 and max of 363.38. The app finds the lowest value from Season 1 and 2 Bid and Offer fields, but because the data shifted, back in October 2016 and April 2016, it is including values from Summer 2016 and Winter 2016 seasons.

Does anyone know if there is a way to rewrite the script so that it would calculate lowest and highest average for two seasons? In other words, for the latest date in the database, February 20, 2017, I need to calculate lowest and highest averages for Bid and Offer values in S-17 and W-17 fields over the last 254 days. On September 20, 2016, however, the app should look at the bid and offer values for the last 254 days of W-16 and S-17 seasons.

If possible, I'd like to keep the script dynamic to avoid me having to rewrite the script every April and October when new fields for new seasons are added.

Any help with this is much appreciated.

Thanks in advance,

Mikhail Bespartochnyy

[mbespartochnyy · Accepted solution]

Solved it! 24 hours of work right here:

/*------------------------------------------------------------------------------------------ Summary

  1.  Bring in distinct dates.

  2.  Bring in first two seasons of bid and offer values

  I.   Bring in data where Season 1 and Season 2 bid and Offers are not null

  II.  Average Season 1 and Season 2 bid and offers

  III. Find Min and Max average values for last 254 days

  IV.  Store Min and Max values in Min$(i) and Max$(i) fields

  V.   Left Join min and max values to Dates loaded in step 1

  3.  Bring in next two seasons with the 5 steps listed above

  4.  Do this from i = 1 to number of seasons

  5.  Create a cross table of the table that was created in steps 1-4

  6.  Move all fields in only few columns

  7.  Keep only first Min and Max columns.

  8.  Create a load script to bring in season values

  9.  Keep only 6 seasons

  10. Left Join table created in step 7 to table created in steps 8 & 9.

--------------------------------------------------------------------------------------------------*/

Power_Min_Max:

LOAD [Deal Date] as Deal_Date

FROM

[Moving Fields 3.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where WeekDay([Deal Date]) = 'Mon' or

   WeekDay([Deal Date]) = 'Tue' or

   WeekDay([Deal Date]) = 'Wed' or

   WeekDay([Deal Date]) = 'Thu' or

   WeekDay([Deal Date]) = 'Fri';

Power_Data_Table:

CrossTable(Attribute, Data)

LOAD *

FROM

[Moving Fields 3.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where WeekDay([Deal Date]) = 'Mon' or

   WeekDay([Deal Date]) = 'Tue' or

   WeekDay([Deal Date]) = 'Wed' or

   WeekDay([Deal Date]) = 'Thu' or

   WeekDay([Deal Date]) = 'Fri';

Power_Data_Table2:

LOAD *,

  'Season_' & AutoNumber(PurgeChar(Upper(Attribute), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '), 'Power_Seasons') & '_' & PurgeChar(Attribute, '0123456789') as Flag

Resident Power_Data_Table

Where WildMatch(Attribute, 'Bid*', 'Offer*')

Order By [Deal Date],

  Attribute;

DROP Table Power_Data_Table;

FOR i = 1 to FieldValueCount('Flag')

  LET vField = FieldValue('Flag', $(i));

  Left Join(Power_Min_Max)

  LOAD [Deal Date] as Deal_Date,

  Data as [$(vField)]

  Resident Power_Data_Table2

  Where Flag = '$(vField)';

NEXT

DROP Table Power_Data_Table2;

Final_Min_Max_Table:

LOAD Distinct Deal_Date

Resident Power_Min_Max;

FOR i = 1 to (NoOfFields('Power_Min_Max') - 1) / 2;

  LET i2 = $(i) + 1;

  IF $(i2) <= (NoOfFields('Power_Min_Max') - 1) / 2 THEN

//--------------------------------------------------------------------------------------- Start Loop

  Min_Max_Table:

  LOAD RowNo() as RowNo,

  Deal_Date,

  Season_$(i)_Bid,

  Season_$(i)_Offer,

  Season_$(i2)_Bid,

  Season_$(i2)_Offer,

  RangeAvg(Season_$(i)_Bid,

   Season_$(i)_Offer,

   Season_$(i2)_Bid,

   Season_$(i2)_Offer) as Season_$(i)_Avg

  Resident Power_Min_Max

  Where Len(Trim(Season_$(i)_Bid)) <> 0 and

   Len(Trim(Season_$(i)_Offer)) <> 0 and

   Len(Trim(Season_$(i2)_Bid)) <> 0 and

   Len(Trim(Season_$(i2)_Offer)) <> 0;

  Left Join(Min_Max_Table)

  LOAD RowNo + IterNo() - 1 as RowNo,

  Season_$(i)_Avg as New_Season_$(i)_Avg

  Resident Min_Max_Table

  While RowNo + IterNo() - 1 <= RowNo + 254;

  Left Join(Final_Min_Max_Table)

  LOAD Deal_Date,

  Season_$(i)_Avg,

  Min(New_Season_$(i)_Avg) as Season_$(i)_Min,

  Max(New_Season_$(i)_Avg) as Season_$(i)_Max

  Resident Min_Max_Table

  Group By RowNo,

  Deal_Date,

  Season_$(i)_Bid,

  Season_$(i)_Offer,

  Season_$(i2)_Bid,

  Season_$(i2)_Offer,

  Season_$(i)_Avg;

  DROP Table Min_Max_Table;

  ENDIF

NEXT

//----------------------------------------------------------------------------------------- End Loop

DROP Table Power_Min_Max;

New_Final_Min_Max_Table:

CrossTable(Attribute, Data)

LOAD *

Resident Final_Min_Max_Table;

New_Final_Min_Max_Table2:

LOAD *,

  'Season_' & AutoNumber(RowNo(), Deal_Date & PurgeChar(Attribute, '0123456789') & 'Power_Seasons2') & '_' & Right(Attribute, 3) as Flag

Resident New_Final_Min_Max_Table

Where WildMatch(Attribute, '*Min*', '*Max*')

Order By Deal_Date,

  Attribute;

DROP Table New_Final_Min_Max_Table;

New_Final_Min_Max_Table3:

LOAD Distinct Deal_Date

Resident New_Final_Min_Max_Table2;

FOR i = 1 to FieldValueCount('Flag')

  LET vField = FieldValue('Flag', $(i));

  Left Join(New_Final_Min_Max_Table3)

  LOAD Deal_Date,

  Data as [$(vField)]

  Resident New_Final_Min_Max_Table2

  Where Flag = '$(vField)';

NEXT

DROP Tables New_Final_Min_Max_Table2, Final_Min_Max_Table;

Power_Seasons:

CrossTable(Attribute, Data)

LOAD *

FROM

[Moving Fields 3.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where WeekDay([Deal Date]) = 'Mon' or

   WeekDay([Deal Date]) = 'Tue' or

   WeekDay([Deal Date]) = 'Wed' or

   WeekDay([Deal Date]) = 'Thu' or

   WeekDay([Deal Date]) = 'Fri';

//---------------------------------------------------------------- Create Flag Field to Track Seasons

Power_Seasons2:

LOAD *,

  'Power_Season_' & AutoNumber(RowNo(), [Deal Date] & PurgeChar(Attribute, '0123456789') & 'Power_Seasons') & '_' & PurgeChar(Attribute, '0123456789') as Flag

Resident Power_Seasons

Where WildMatch(Attribute, 'Bid*', 'Offer*')

Order By [Deal Date],

  Attribute;

DROP Table Power_Seasons;

//------------------------------------------- Bring in List of Dates By Which Data Will Be Organized

Power_Seasons3:

LOAD DISTINCT [Deal Date] as Deal_Date

Resident Power_Seasons2;

//-------------- Loop Through Each Flag And Store Values Associated With Each Flag In Distinct Field

FOR i = 1 to FieldValueCount('Flag')

  LET vField = FieldValue('Flag', $(i));

  Left Join(Power_Seasons3)

  LOAD [Deal Date] as Deal_Date,

  Data as [$(vField)]

  Resident Power_Seasons2

  Where Flag = '$(vField)';

NEXT

DROP Table Power_Seasons2;

Power_Seasons:

LOAD RowNo() as RowNo,

  Deal_Date,

//-------- 1 Season Ahead

  Power_Season_1_Bid,

  Power_Season_1_Offer,

//-------- 2 Seasons Ahead

  Power_Season_2_Bid,

  Power_Season_2_Offer,

//-------- 3 Seasons Ahead

  Power_Season_3_Bid,

  Power_Season_3_Offer,

//-------- 4 Seasons Ahead

  Power_Season_4_Bid,

  Power_Season_4_Offer,

//-------- 5 Seasons Ahead

  Power_Season_5_Bid,

  Power_Season_5_Offer,

//-------- 6 Seasons Ahead

  Power_Season_6_Bid,

  Power_Season_6_Offer

Resident Power_Seasons3

Order By Deal_Date;

DROP Table Power_Seasons3;

Left Join(Power_Seasons)

LOAD Deal_Date,

  Season_1_Max,

  Season_1_Min,

  Season_2_Max,

  Season_2_Min

Resident New_Final_Min_Max_Table3;

DROP Table New_Final_Min_Max_Table3;

DROP Field RowNo;

[sunny_talwar]

Very nice

[mbespartochnyy]

Thanks I'm so glad it's done. Your comment on the other thread about keeping min and max for each season helped.