Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

DepartArriveDifference
00:02:0000:03:0000:01:00
00:02:0000:02:00Zero Diff
00:02:0000:05:0000:03:00
00:02:0000:02:00Zero Diff
00:02:0000:05:0000:03:00
00:02:0000:04:0000:02:00
00:02:0000:02:0000:00:00

diff = arrive - depart

Oh I updated now please check


Thanks

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

];

===================

View solution in original post

8 Replies
swuehl
MVP
MVP

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.

its_anandrjs

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

Not applicable
Author

Yes you are right please check now i am updated the data.

Thanks

MK_QSL
MVP
MVP

Check enclosed file...

Not applicable
Author

Manish i am using personal edition so i am not able to open your file please provide me script.

its_anandrjs

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;

MK_QSL
MVP
MVP

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

];

===================

Not applicable
Author

Thanks Anand and Manish both answers are correct thanks once again to you all.