String Function Help !

[ganeshreddy]

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

[anbu1984 · Accepted solution]

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

];

[Anonymous]

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

[ganeshreddy]

Thank you Anbu.

[#5]

Great answet. Good Job

[#6]

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; 

[Anonymous]

Missed that while copying. Thanks for pointing it out.