1 Reply Latest reply: Jan 23, 2014 1:56 PM by Ajay Krishnan Prabhakaran RSS

    changing structure of data based on Machines's Log Files

      Hi all,

       

      Thank you in advance for reading this.

       

      I got some txt log files of a huge factory with a lot of machines and all kind of errors. One row is one event, And events are listed with asc time. Here is a small extract :

       

      3930| 88|3066|5411|UPD1_BF1122|xx

      3930| 88|3067|5456|UPD1_BF1122|xx

      3931| 88|3066|5419|UPD1_BF1122|xx

      3931| 88|3067|5458|UPD1_BF1122|xx 931| 88|3341|PDC-WE2_BF1066|xx

      3931| 88|3341|PDC-WE_BF1066|xx

      3932| 88|3067|5459|UPD1_BF1122|xx

      3932| 88|3075|5459|UPD1_BF1122|xx

      3932| 88|3076|5458|UPD1_BF1122|xx

       

      I successfully split it to make it clean in a table with these fields :

       

      TIMESTAMP | CODE | EQUIPMENT NAME

       

      Here is my problem. It is one week i am stuck on it.

       

      For one error, the log gives me two rows : one for the error start, and one for the error end :

       

      H start    | start code   | equipment

      ....

      ....

      (  many many rows between these two rows)

      ...

      ...

      H end     | end code    | equipment

       

      And i would like to make a new table with only one row per event by adding one time field :

       

      H start | H end | error | equipment

       

      Some details :

      - the end row can be very very far to the start row

      - the end code is always ONE same value for all error and all equipment. " 3024"

      - for the same equipment , some other rows / events can exist between the two start and end rows / event

       

      My idea is to make a load, copy everything of the start row, and at the extra field, go search below on the table the value in the field TIMESTAMP of the closest row where the field CODE is = 3024 (end code) and EQUIPMENT = same value than the current start row. With no success till now ....

       

      I hope I am not too confused in my explanations. Thank you in advance. Please feel free to ask any more details.


      Regards and Happy new year !

       

      Ju