Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ganeshreddy
Creator III
Creator III

String Function Help !

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

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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

]
;

View solution in original post

6 Replies
anbu1984
Master III
Master III

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

]
;

View solution in original post

greeshma_k
Creator III
Creator III

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
Creator III
Creator III
Author

Thank you Anbu.

Not applicable

Great answet. Good Job

Not applicable

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; 

greeshma_k
Creator III
Creator III

Missed that while copying. Thanks for pointing it out.