Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
New col value is 15 in place of 10.
Unable to understand your logic ! Please describe in little more depth.
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;
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;
Input:
Load
Date#(Date, 'DD MMMM YYYY') as Date,
Time#(Time, 'hh:mm') as Time,
Value
Inline [
Date, Time , Value12 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;