Urgent Help

[soha1902]

Hi All,

I have following data:

Date               Time               Value

12 june 2015     10:00               25

12 june 2015     9:00                   30

12 june 2015     8:00                    35

11 june 2015     11:00               20

11june 2015         10:00               35

My Requirement is

Date               Time               Value      NewCol

12 june 2015     10:00               25              5

12 june 2015     9:00                   30          5

12 june 2015     8:00                    35          0

11 june 2015     11:00               20               10

11june 2015         10:00               35              0

Logic: for smallest time value would be 0 for every similar date and other value will be Value - Previous(Value). I want to implement this in Script level only. I am using personal edition so plz share the script/code.

Thanks

[soha1902]

New col value is 15 in place of 10.

[MK_QSL]

Unable to understand your logic ! Please describe in little more depth.

[MK_QSL]

Data:
Load
  Date(Date#(Date,'DD MMMM YYYY')) as Date,
  Time(Time#(Time,'h:mm'),'hh:mm') as Time,
  Value
Inline
[
  Date,         Time,   Value
  12 june 2015, 10:00,  25
  12 june 2015, 9:00,   30
  12 june 2015, 8:00,   35
  11 june 2015, 11:00,  20
  11 june 2015, 10:00,  35
];


Left Join (Data)
Load 
  Date,
  Time(Min(Time),'hh:mm') as MinTime
Resident Data
Group By Date;


Left Join (Data)
Load 
  Date, 
  Time,  
  1 as Flag
Resident Data Where Time = MinTime;


NoConcatenate
Final:
Load 
  Date,
  Time,
  Value,
  If(Flag = 1, 0, FABS(Value - Previous(Value))) as NewCol
Resident Data
Order By Date, Time;


Drop Table Data;

[swuehl · Accepted solution]

Data:  
Load  
  Date,  
  Time,
  Value  
Inline  
[  
  Date,         Time,   Value  
  12 june 2015, 10:00,  25  
  12 june 2015, 9:00,   30  
  12 june 2015, 8:00,   35  
  11 june 2015, 11:00,  20  
  11 june 2015, 10:00,  35  
];  

Result:
LOAD Date, Time, Value, 
  if(peek(Date) = Date, Rangesum(Peek(Value),-Value),0) as NewVal
RESIDENT Data
ORDER BY Date, Time asc;


DROP TABLE Data;

[tresesco]

Input:
Load
  Date#(Date, 'DD MMMM YYYY') as Date,
  Time#(Time, 'hh:mm') as Time,
  Value
Inline [
Date,               Time ,              Value

12 june 2015,     10:00 ,              25

12 june 2015,     9:00  ,                 30

12 june 2015,     8:00  ,                  35

11 june 2015 ,    11:00   ,            20

11 june 2015 ,        10:00   ,            35

];
Output:
Load
Date,
Time,
If(RowNo()=1 or Date<>Previous(Date), 0, RangeSum(Previous(Value),-Value)) as NewValue,
Value
Resident Input Order By Date, Time;

Drop Table Input;