2 Replies Latest reply: May 22, 2012 3:52 AM by Miguel Angel Baeyens de Arce RSS

    Calculate Difference in the Script

      Hello Community,

       

      i have a table that contains Data and Values (the QV-File). I want to show the Difference between 1 , 2 , 3 , 4 in an extra column. It is possible to do this in a Pivot-Table. But i want to do this in the Script. I tried to edit my skript that the difference  should be shown. But it doesn't work.  Has someone got an idea? The Excel-File shows the final table, that the script should calculate. Thanks for your help!

      best regards, Aylin

        • Calculate Difference in the Script
          Martin Pohl

          my idea:

          load facts, add rowno() as Row

          left join load

          Row+1 as Row

          fact as oldfact

          additional fileds for compare (eg customer as oldcustomer) as old

           

          load

          if(customer=oldcustomer) fact-oldfact as diff

          resident facts

          Regards

          • Re: Calculate Difference in the Script
            Miguel Angel Baeyens de Arce

            Hi,

             

            Another way to get that done, depending on how structured is your data, is using Previous() function, here is an example:

             

            OriginalDataTemp:
            LOAD * INLINE [
                 Key,Data,Value
                 1,ABC 1b, 0.043
                 2,ABC 2f,  0.044
                 3,ABC 3f,  0.044
                 1,ABC 1f,  0.045
                 3,ABC 3b,  0.049
                 2,ABC 2b,  0.030
                 4,ABC 4b,  0.052
                 4,ABC 4f,  0.053
            ];
            
            DifferenceTable:
            LOAD Key,
                 Data,
                 Value,
                 If(Left(Data, 5) = Left(Previous(Data), 5), Value - Previous(Value)) AS Difference
            RESIDENT OriginalDataTemp
            ORDER BY Data;
            
            DROP TABLE OriginalDataTemp;
            

             

            Hope that helps.

             

            Miguel