Calculate Difference in the Script

[#1]

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

[martinpohl]

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

[Miguel_Angel_Baeyens · Accepted solution]

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