Transformation help!

[ganeshreddy]

Hi all,

How to find valid meal break, first meal time out and second meal time out from below given data,

Ex: 22:15:00-24:00:00|dayDivide|00:00:00-02:30:00|Break|03:00:00-06:46:00.

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

    00:00:00-03:30:00

    00:00:00-02:51:30|Break|03:23:00-04:30:00

    00:00:00-05:30:00

];

From the above data how to get these fields valid meal break, first meal time out and second meal time out.

Cheers,

Ganesh

[danieloberbilli · Accepted solution]

Might be that I didnt get it quite right...but it should help to understand how to...

Please find qvw attached

[danieloberbilli]

pfa

[ganeshreddy]

Hi Daniel,

Can you help me to find logic like this, traverse through the row and find 'Break' key word and record left instance to 'Break' as Out_Time and right instance as In_Time. It will be helpful for me because our previous logic won't work for below data.

Ex: 

22:15:00-24:00:00|Transfer|schedTransfer|00:00:00-02:30:00|Break|03:00:00-06:46:00.

00:00:00-02:51:30|dayDivide|03:23:00-04:00:00|Break|04:30:30-12:07:00|transfer

00:00:00-03:30:00|Transfer|schedTransfe|00:00:00-02:51:30|Break|03:23:00-04:30:00|Transfer|schedTr|00:00:00- 05:30:00

[danieloberbilli]

Thats getting pretty complex. You could use index() to search for the position of Break...but maybe even this is not enough for all your data. Maybe you should split the string in several fields or store them as several values in one field underneath each other...just some idea. Please post this again in a new thread - I think this needs some time and ideas of other users