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
