changing structure of data based on Machines's Log Files

Thu, 23 Jan 2014 18:39:24 GMT

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 !

Re: changing structure of data based on Machines's Log Files

Thu, 23 Jan 2014 18:56:56 GMT

Hey Julien,

I think I understand what you want to do. Please check out the attached file and see if it solves your issue.

There would be an inline table where same codes will have two different times in two rows. I used Group by and Concat to make it appear in the same row.

Hope it helps

Thanks

topic Re: changing structure of data based on Machines's Log Files in QlikView

changing structure of data based on Machines's Log Files

Re: changing structure of data based on Machines's Log Files