Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

Tags (2)
1 Reply
Not applicable

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

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

AJ