Calculated column based on two values in single column in SAP HANA

[Former Member]

Hi All,

I have a scenario in which a calculated column/ a column needs to be created from 2 or more values taken from a single column based on the timestamp

Let say the below is a table with following values,

date                    time               measure    

08-30-2014        6:00:00AM     10         

08-30-2014        6:00:10AM     20        

08-30-2014        6:02:05AM     45        

08-30-2014        6:04:23AM     35         

now I need to create a column whose calculation are  like find the previous timestamp and find the difference between the current and previous Measure

timestamp inverval are not the same like shown in the table the entry can be at 6 AM and then6:00:05 (5 seconds interval) and next entry is at 6:02:05 which is more that 1 min

date                    time               Measure     calculated column               Comment

08-30-2014        6:00:00AM     10               0                                   since there is no pervious entry hence 0

08-30-2014        6:00:10AM     20               10                                  difference between measure of current row and previous timestamp entry (ie in                                                                                                               this case 6:00:00AM

08-30-2014        6:02:05AM     45               25

08-30-2014        6:04:23AM     35               -10

One way of doing this is using Rank Function and self join using rank ,

temp table 1- select  date, time, measure, rank() over (order by date,time) as Rank;

date                    time               measure     Rank

08-30-2014        6:00:00AM     10               1    

08-30-2014        6:00:10AM     20              2

08-30-2014        6:02:05AM     45              3

08-30-2014        6:04:23AM     35               4

select a.date,a.time, a.measure,a.rank b.date,b.time, b.measure,b.rank,

from temp table1 as a

right outerjoin

temp table 1 as b

on( a.Rank = b.rank+1);

a.date                   a. time          a. measure     a.Rank        b.date                   b. time              b. measure          b.Rank         calculated column

?                              ?               ?                             ?             08-30-2014        6:00:00AM          10                         1                     ?

08-30-2014        6:00:00AM     10                         1               08-30-2014        6:00:10AM          20                         2                    10

08-30-2014        6:00:10AM     20                         2               08-30-2014        6:02:05AM          45                        3                      25

08-30-2014        6:02:05AM     45                        3                08-30-2014        6:04:23AM          35                         4                     -10

How can I implement it in Analytic view/ calculation view ?since Rank is not supported

is there is any other way of doing this?

Please help!!

Thank you

-Gayathri

[henrique_pinto · Accepted solution]

Hi Gayathri,

the easiest way to implement this in SQL would be with the LAG() window function, no need for temp tables or self joins. Window Functions - SAP HANA SQL and System Views Reference - SAP Library

It could be something like:

SELECT DATE, TIME, MEASURE, (MEASURE - LAG(MEASURE, 1) OVER (ORDER BY DATE, TIME)) AS MEASURE_DIF FROM TABLE;

Use coalesce over the calculated column if you'd like the null value to be 0.

And as mentioned, you can encapsulate this SQL code in a scripted calculation view.

Best,

Henrique.

[Former Member]

Thanks for the help.

[pfefferf]

Hi Gayathri,

you can go with your approach within a Scripted Calculation View (using SQL Script).

1) Point to the required data.

2) Sort the data.

3) Create calculated "rank" column.

4) Join your data as described.

5) Calculate the date/time difference.

6) ...

Regards,

Florian