topic String Function Help ! in QlikView

String Function Help !

ganeshreddy — Wed, 29 Apr 2015 07:10:49 GMT

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

Re: String Function Help !

anbu1984 — Wed, 29 Apr 2015 07:17:25 GMT

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

];

Re: String Function Help !

Anonymous — Wed, 29 Apr 2015 07:20:55 GMT

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

Re: String Function Help !

ganeshreddy — Wed, 29 Apr 2015 07:24:47 GMT

Thank you Anbu.

Re: String Function Help !

Wed, 29 Apr 2015 07:27:07 GMT

Great answet. Good Job

Re: String Function Help !

Wed, 29 Apr 2015 07:29:56 GMT

Table t1 has not been found, it should be like this:

Re: String Function Help !

Anonymous — Wed, 29 Apr 2015 07:35:08 GMT

Missed that while copying. Thanks for pointing it out.