2 Replies Latest reply: Feb 17, 2018 10:07 AM by Neelima Bushpala RSS

    data subtraction calculation methos

    Neelima Bushpala

      Hi all,

       

      Could any one please help.

       

      Out put required is (week & material & Loc) week 6 - DFC Days should be subtracted from  (week & material & Loc) week 7  - DFC Days. the out put of this subtraction is in the new column. This way it should subtracted from week 8 - week 7, week 9- week 8 for 52 weeks.


      Data Examples of input


          

      Input
      WeekMaterialLocDFC Days
      W06-181234USD4
      W06-182345IND230
      W06-183456FR450
      W06-181234UK36
      W06-189568UK41
      W07-181234USD9
      W07-182345IND237
      W07-189568UK46
      W06-183456FR455
      W07-181234UK41
      W08-189568UK51
      W08-181234USD14
      W08-182345IND244
      W08-183456FR460
      W08-181234UK46

       

       

      Out put required is as follows

       

           

      Output
      WeekMaterialLocDFC DaysDifference in Days
      W06-181234USD45
      W06-182345IND2307
      W06-183456FR4505
      W06-181234UK365
      W06-189568UK415
      W07-181234USD95
      W07-182345IND2377
      W07-189568UK465
      W06-183456FR4555
      W07-181234UK415
      W08-189568UK51-51
      W08-181234USD14-14
      W08-182345IND244-244
      W08-183456FR460-460
      W08-181234UK46-46
        • Re: data subtraction calculation methos
          Sunny Talwar

          Try this

           

          Table:

          LOAD * INLINE [

              Week, Material, Loc, DFC Days

              W06-18, 1234, USD, 4

              W06-18, 2345, IND, 230

              W06-18, 3456, FR, 450

              W06-18, 1234, UK, 36

              W06-18, 9568, UK, 41

              W07-18, 1234, USD, 9

              W07-18, 2345, IND, 237

              W07-18, 9568, UK, 46

              W07-18, 3456, FR, 455

              W07-18, 1234, UK, 41

              W08-18, 9568, UK, 51

              W08-18, 1234, USD, 14

              W08-18, 2345, IND, 244

              W08-18, 3456, FR, 460

              W08-18, 1234, UK, 46

          ];


          FinalTable:

          LOAD *,

          If(Material = Previous(Material) and Loc = Previous(Loc), Previous([DFC Days]) - [DFC Days], -[DFC Days]) as [Difference in Days]

          Resident Table

          Order By Material, Loc, Week desc;


          DROP Table Table;

           

          Capture.PNG