Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions

Re: Calculate Difference in the Script

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

2 Replies
martinpohl
Valued Contributor II

Calculate Difference in the Script

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

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

Community Browser