topic tricky text file transformation in QlikView

tricky text file transformation

hectorgarcia — Fri, 01 Jan 2010 01:21:35 GMT

I need to analize a a logfile in txt fromat.

a single record is normally received in one line but in many cases a record information is in several lines (much times up to 500 times)

What I need is to have in a single line all the children lines that have more than 1 line per record.

For security reasons i cannot upload the file but i'm attaching the conceptual struture of the logfile.

This is my current challenge, once i solve this i will easily read and extract any information on each record using string scripting techniques (subfiled, match etc)

any help will be greatly appreciated.

hector

tricky text file transformation

prieper — Fri, 01 Jan 2010 21:14:57 GMT

Which is the identifyer and which data do you need to extract?

tricky text file transformation

hectorgarcia — Fri, 08 Jan 2010 14:43:14 GMT

Hi peter, I'm not worried about the data itself since i'm very clear about the scripting techniques to extract data from flat files or any other non conventional datasource using string functions...the challenge i'm having for now is how to manipulate with qilkview a file that is comming with the structure I described.

Currently what i'm doing is to manipulate the file with a textpad macro that performs 2 tasks:

1) Find every new line indicator (\n) and replace it by a white space (this convert the entire file in a single line.....)

2) Then Find and replace the regular expression </GLTData> for </GLTData>\n

The problem is that this is a very manual procedure and the files size are too big and are many files to process so it is very "expensive" to do that

I want to use the qlikview power to perform this transformation with some scripting techniques since the wizard is not helping me to obtain the same result WITH NO EXTRA MANIPULATION OF THE DATA

Please find attached a ppt that will show the current problem and how the workaround applied is making the "expensive' trick

tricky text file transformation

Sun, 10 Jan 2010 01:31:18 GMT

If we treat finding </GLTData> as the end of line segments to be constructed, the attached file will work. Take a look.

Tom

tricky text file transformation

prieper — Sun, 10 Jan 2010 21:37:05 GMT

Clever idea to examine for the end of line.

Peter

tricky text file transformation

hectorgarcia — Mon, 11 Jan 2010 18:52:57 GMT

THANKS TOM, VERY CREATIVE, CONGRATULATIONS, NOW I'M ABLE TO CONTINUE WITH MY TRANSFORMATION WITH NO PROBLEMS, TANKS A LOT!!!!!!!!!

HECTOR