Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please help me on finding Actual in, Actual out by using below data. Here the instance highlighted by green are Actual in,instance highlighted by red are actual out how to fetch them as Actual In, Actual Out.
LOAD * INLINE [
Punches
22:15:00-24:00:00|Break|00:00:00-02:30:00|Break|03:00:00-06:46:00.
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|dayDivide
00:00:00-03:30:00
00:00:00-02:51:30|Break|03:23:00-04:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00
00:00:00-05:30:00
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|transfer
Break|00:00:00-02:51:30|Break|03:23:00-04:30:00
scheduuledNewShift|00:00:00-05:30:00|Break|06:00:00-12:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00|scheduledOutWorkRule
18:00:00-24:00:00|Transfer|schedTransfer|00:00:00-25:00:00|Break|23:30:00-24:00:00|dayDivide
];
cheers,
Ganesh
LOAD *,Right(SubField(Punches,'-',1),8) As ActualIn,Left(SubField(Punches,'-',-1),8) As ActualOut INLINE [
Punches
22:15:00-24:00:00|Break|00:00:00-02:30:00|Break|03:00:00-06:46:00.
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|dayDivide
00:00:00-03:30:00
00:00:00-02:51:30|Break|03:23:00-04:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00
00:00:00-05:30:00
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|transfer
Break|00:00:00-02:51:30|Break|03:23:00-04:30:00
scheduuledNewShift|00:00:00-05:30:00|Break|06:00:00-12:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00|scheduledOutWorkRule
18:00:00-24:00:00|Transfer|schedTransfer|00:00:00-25:00:00|Break|23:30:00-24:00:00|dayDivide
];
LOAD *,Right(SubField(Punches,'-',1),8) As ActualIn,Left(SubField(Punches,'-',-1),8) As ActualOut INLINE [
Punches
22:15:00-24:00:00|Break|00:00:00-02:30:00|Break|03:00:00-06:46:00.
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|dayDivide
00:00:00-03:30:00
00:00:00-02:51:30|Break|03:23:00-04:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00
00:00:00-05:30:00
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|transfer
Break|00:00:00-02:51:30|Break|03:23:00-04:30:00
scheduuledNewShift|00:00:00-05:30:00|Break|06:00:00-12:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00|scheduledOutWorkRule
18:00:00-24:00:00|Transfer|schedTransfer|00:00:00-25:00:00|Break|23:30:00-24:00:00|dayDivide
];
Hi,
Try this
LOAD * INLINE [
Punches
22:15:00-24:00:00|Break|00:00:00-02:30:00|Break|03:00:00-06:46:00
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|dayDivide
00:00:00-03:30:00
00:00:00-02:51:30|Break|03:23:00-04:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00
00:00:00-05:30:00
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|transfer
Break|00:00:00-02:51:30|Break|03:23:00-04:30:00
scheduuledNewShift|00:00:00-05:30:00|Break|06:00:00-12:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00|scheduledOutWorkRule
18:00:00-24:00:00|Transfer|schedTransfer|00:00:00-25:00:00|Break|23:30:00-24:00:00|dayDivide
];
t2:
load
if(index(SubField(Punches,'-',1),'|')>0, SubField( SubField(Punches,'-',1),'|',2),SubField(Punches,'-',1)) as ActualIn,
if(index(SubField(Punches,'-',-1),'|')>0,SubField( SubField(Punches,'-',-1),'|',-2),SubField(Punches,'-',-1)) as ActualOut
Resident t1;
Regards,
Greeshma
Thank you Anbu.
Great answet. Good Job
Table t1 has not been found, it should be like this:
t1:
LOAD * INLINE [
Punches
22:15:00-24:00:00|Break|00:00:00-02:30:00|Break|03:00:00-06:46:00
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|dayDivide
00:00:00-03:30:00
00:00:00-02:51:30|Break|03:23:00-04:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00
00:00:00-05:30:00
00:00:00-02:51:30|Break|03:23:00-04:00:00|Break|04:30:30-12:07:00|transfer
Break|00:00:00-02:51:30|Break|03:23:00-04:30:00
scheduuledNewShift|00:00:00-05:30:00|Break|06:00:00-12:30:00
22:15:00-24:00:00|Break|00:00:00-02:30:00|scheduledOutWorkRule
18:00:00-24:00:00|Transfer|schedTransfer|00:00:00-25:00:00|Break|23:30:00-24:00:00|dayDivide
];
t2:
load
if(index(SubField(Punches,'-',1),'|')>0, SubField( SubField(Punches,'-',1),'|',2),SubField(Punches,'-',1)) as ActualIn,
if(index(SubField(Punches,'-',-1),'|')>0,SubField( SubField(Punches,'-',-1),'|',-2),SubField(Punches,'-',-1)) as ActualOut
Resident t1;
Missed that while copying. Thanks for pointing it out.