- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Flag for time difference
Hello All,
I have the data for flight arrivals and departures and from that data i want to get difference and if difference is 0 then flag Zero Difference to be added. How comes this situation please help.
Depart | Arrive | Difference |
00:02:00 | 00:03:00 | 00:01:00 |
00:02:00 | 00:02:00 | Zero Diff |
00:02:00 | 00:05:00 | 00:03:00 |
00:02:00 | 00:02:00 | Zero Diff |
00:02:00 | 00:05:00 | 00:03:00 |
00:02:00 | 00:04:00 | 00:02:00 |
00:02:00 | 00:02:00 | 00:00:00 |
diff = arrive - depart
Oh I updated now please check
Thanks
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Script.
==========
Load
RowNo() as NO,
Time#(Depart,'hh:mm:ss') as Depart,
Time#(Arrive,'hh:mm:ss') as Arrive,
IF(Time#(Arrive,'hh:mm:ss')-Time#(Depart,'hh:mm:ss')='00:00:00','No Diff',Time(Time#(Arrive,'hh:mm:ss')-Time#(Depart,'hh:mm:ss'))) as Difference,
IF(Interval(Arrive-Depart,'hh:mm:ss')='00:00:00','No Diff',Interval(Arrive-Depart,'hh:mm:ss')) as Flag
Inline
[
Depart, Arrive
00:02:00, 00:03:00
00:02:00, 00:02:00
00:02:00, 00:05:00
00:02:00, 00:02:00
00:02:00, 00:05:00
00:02:00, 00:04:00
00:02:00, 00:02:00
];
===================
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, I don't fully understand. You get 'Zero Diff' even when the difference is apparently not zero?
Please post the expression you are using or the script code.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your data is not proper or may be you asking difference of arrive and depart so do like
if( (Time(Arrive,'hh:mm:ss') - Time(Depart,'hh:mm:ss') ) ='00:00:00','Zero diff', Time(Time(Arrive,'hh:mm:ss') - Time(Depart,'hh:mm:ss'))) as Diff
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes you are right please check now i am updated the data.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Check enclosed file...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Manish i am using personal edition so i am not able to open your file please provide me script.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can load like
Temp:
LOAD *,RecNo() as Rowid;
LOAD * INLINE [
Depart, Arrive, Difference
00:02:00, 00:03:00, 00:01:00
00:02:00, 00:02:00, Zero Diff
00:02:00, 00:05:00, 00:03:00
00:02:00, 00:02:00, Zero Diff
00:02:00, 00:05:00, 00:03:00
00:02:00, 00:04:00, 00:02:00
00:02:00, 00:02:00, 00:00:00
];
LOAD *,
if(Time#(Arrive,'hh:mm:ss') - Time#(Depart,'hh:mm:ss') ='00:00:00','Zero diff',Time(Time#(Arrive,'hh:mm:ss') - Time#(Depart,'hh:mm:ss'))) as Diff,
Resident Temp;
DROP Table Temp;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Script.
==========
Load
RowNo() as NO,
Time#(Depart,'hh:mm:ss') as Depart,
Time#(Arrive,'hh:mm:ss') as Arrive,
IF(Time#(Arrive,'hh:mm:ss')-Time#(Depart,'hh:mm:ss')='00:00:00','No Diff',Time(Time#(Arrive,'hh:mm:ss')-Time#(Depart,'hh:mm:ss'))) as Difference,
IF(Interval(Arrive-Depart,'hh:mm:ss')='00:00:00','No Diff',Interval(Arrive-Depart,'hh:mm:ss')) as Flag
Inline
[
Depart, Arrive
00:02:00, 00:03:00
00:02:00, 00:02:00
00:02:00, 00:05:00
00:02:00, 00:02:00
00:02:00, 00:05:00
00:02:00, 00:04:00
00:02:00, 00:02:00
];
===================
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Anand and Manish both answers are correct thanks once again to you all.