Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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


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

Tags:
Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question