4 Replies Latest reply: Feb 13, 2014 8:59 AM by shay raber RSS

    incremental data

    shay raber

      Hi,

       

      I'm facing an issue while trying to create (in the script, not at the GUI) the following:

      I have a table with few dimensions and a value field.

      I wish to create a new table with incremental value as the new data for each key record (combination of all the dimensions). I'll try to explain with an image. the image shows 2 tables (the left one is the "before" table which I have and the right one is the "after" table which I wish to have).

      In the image I've putted a dummy data just for the explanation of my problem (I've highlighted as an example cells which sums into a new cell at the result table).

      Please assist.

       

      Thanks and best regards,

      Shay

      Untitled.png

        • Re: incremental data
          Massimo Grossi

          could you post your data (excel, txt, ....)?

          thanks

            • Re: Re: incremental data
              Massimo Grossi

              SCRIPT

               

              Directory;

               

              Source:

              LOAD YearMonth,

                  Year,

                  Month,

                  CompID,

                  Item,

                  Value

              FROM

              [107297.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

              Table:

              NoConcatenate

              LOAD YearMonth,

                  Year,

                  Month,

                  CompID,

                  Item,

                  Value,

                  if(Item<>peek(Item) or CompID<>peek(CompID), Value, peek(RollingValue) + Value) as RollingValue

              Resident Source

              order by Item, CompID, Year, Month;

               

              DROP Table Source;

               

              RESULT

               

              YearMonthYearMonthCompIDItemValueRollingValue
              20131111A12841284
              20131111B572572
              20131222A536536
              20131222B822822
              20132111A11672451
              20132111B11701742
              20132222A7801316
              20132222B9161738
              20133111A11573608
              20133111B12823024
              20133222A14792795
              20133222B12693007
              20134111A8684476
              20134111B10034027
              20134222A10043799
              20134222B7733780
              20135111A12685744
              20135111B6694696
              20135222A12555054
              20135222B12225002
              20136111A6086352
              20136111B12375933
              20136222A7235777
              20136222B7155717
                • Re: Re: incremental data
                  shay raber

                  hi Massimo,

                   

                  I'm sorry but it doesn't work for some reason.

                  I'm attaching here an excel file with both the source table and the result.

                  I have filtered the data in order to show an example why the result is not as expected.

                  i don't what's the reason to get different result than you

                  It works!!

                  thanks

                  i needed to add another code as can be seen at the screen shot below

                  Thanks MassimoUntitled.png

                • Re: Re: incremental data
                  shay raber

                  here is a part of my data