3 Replies Latest reply: Mar 2, 2017 12:33 PM by Mikhail Bespartochnyy RSS

    Moving Fields 12 Months Max and Min

    Mikhail Bespartochnyy

      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

        • Re: Moving Fields 12 Months Max and Min
          Mikhail Bespartochnyy

          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;