2 Replies Latest reply: Feb 2, 2018 10:02 AM by Martin Hamilton RSS

    Create a resident table to compare current and previous week

    Martin Hamilton

      Hi

       

      I wish to create a resident table which summarizes invoice information which has been previously loaded into a resident table which creates a current week value and previous week value allowing me to compare and include within an extension I am using.

       

      As part of the load I am also including the week number (using AutoNumber(weekyear(INVOICE_DATE) &'|' & week(INVOICE_DATE)) as WeekSerial)

       

       

       

      CUSTOMER_TYPEINVOICE_AMOUNTINVOICE_DATEWeekSerial
      Type A90801/01/20181
      Type B646601/01/20181
      Type C6301/01/20181
      Type D25663601/01/20181
      Type A9879808/01/20182
      Type B3423508/01/20182
      Type C2567608/01/20182
      Type D7474708/01/20182
      Type A252515/01/20183
      Type B6367815/01/20183
      Type C85615/01/20183
      Type D579915/01/20183
      Type A5457423/01/20184
      Type B84898923/01/20184
      Type C363323/01/20184
      Type D436723/01/20184

       

      I want the resident table to group by CUSTOMER_TYPE but only including the Current & Previous Weeks summed totals.

       

      So assuming we are in week 4 of the month I would expect the following in a new resident table, I dont know how to work out the previous week or even select that to summarise into a column.

       

         

      CUSTOMER_TYPECURRENT_WEEKPREVIOUS_WEEK
      TYPE_A545742525
      TYPE_B84898963678
      TYPE_C3633856
      TYPE_D43675799

       

      Is this even the best way to do it?

       

      Any help appreciated.

       

      Thanks

       

      Martin

        • Re: Create a resident table to compare current and previous week
          Sunny Talwar

          May be try this

           

          Table:

          LOAD * INLINE [

              CUSTOMER_TYPE, INVOICE_AMOUNT, INVOICE_DATE, WeekSerial

              Type A, 908, 01/01/2018, 1

              Type B, 6466, 01/01/2018, 1

              Type C, 63, 01/01/2018, 1

              Type D, 256636, 01/01/2018, 1

              Type A, 98798, 08/01/2018, 2

              Type B, 34235, 08/01/2018, 2

              Type C, 25676, 08/01/2018, 2

              Type D, 74747, 08/01/2018, 2

              Type A, 2525, 15/01/2018, 3

              Type B, 63678, 15/01/2018, 3

              Type C, 856, 15/01/2018, 3

              Type D, 5799, 15/01/2018, 3

              Type A, 54574, 23/01/2018, 4

              Type B, 848989, 23/01/2018, 4

              Type C, 3633, 23/01/2018, 4

              Type D, 4367, 23/01/2018, 4

          ];


          Left Join (Table)

          LOAD Max(WeekSerial) as Max,

          Max(WeekSerial, 2) as Max2

          Resident Table;


          AggrTable:

          LOAD CUSTOMER_TYPE,

          Sum(If(WeekSerial = Max, INVOICE_AMOUNT)) as CURRENT_WEEK,

              Sum(If(WeekSerial = Max2, INVOICE_AMOUNT)) as PREVIOUS_WEEK

          Resident Table

          Group By CUSTOMER_TYPE;