Load multiple lines as a free-text field

[shane_spencer]

I've got a log file that I'm trying to process. The start of each new record is "Number of executions" and the last field is "Statement text". Unfortunately this Statement text may extend to several lines but I want everything after "Statement text =" to be loaded in to a single field. I've tried several different things but have not found a solution. Is this possible?

[Peter_Cammaert]

Did you try this?

• Create a mapping table with all keywords (e.g. terms that precede an equal sign)
• Load the log line by line, add a record number (for later reordering) and put all input text in a single field..
• If you detect an equal sign in this single field, AND the keyword is present in your mapping table, process the data.
• If you detect an equal sign in this single field, AND the keyword is "Statement text", set the CONCATENATELATERFLAG to 1..
• If you detect no equal sign, or the keyword is not present in the mapping table AND the field does not start with a space character, set a CONCATENATELATERFLAG to 1.

• Separate all rows with the FLAG set to 1 from the others, and concatenate them byt using GROUP BY and CONCAT().

• Reinsert the concatenated lines by using the record number field.

Just a rough sketch,

Peter

[shane_spencer]

I was hoping for something simple but did have some thoughts along these lines (though not an idea as fully formed as this). I don't think I can use the "=" (equal sign) though as any kind of delimiter or matching text as it may appear in the Statement text.

[Peter_Cammaert]

Yes you can use the equal sign as a delimiter. There are three conditions that must be met in order to detect a valid parameter line:

• Line starts with a space
• Keyword exists in predefined mapping table
• Keyword is followed by an equal sign.

IMHO text that matches these conditions cannot be mistaken for legal SQL, not even part of it. Thank god for those verbose keywords.

Processing all these conditions in every column creation code won't be simple however. But then, QlikView is not really a parsing engine...

[perumal_41]

Hi

PFA,I hope it help to you

[shane_spencer]

The coding is brilliant, but how do we link the records together so for example we know which "Number of executions" goes with which "Statement text"?

[perumal_41]

Hi,

Please paste sample output  how you want ?

[Peter_Cammaert]

Just assign a counter to every record, whose value only increases upon encountering the next line with keyword "Number of executions". It seems that this keyword is always the first. In this way you'll be counting parameter blocks and reinserting the "Statement text" will be easy.

There is an example of such a numbering in this thread: Numbering Rows more examples?

[shane_spencer]

Well basically each fieldname to the left of the "=" sign will become a header. i.e.

I've just put a few fields as a quick example.

[daveamz]

Hi Shane,

See the script attached.

Regards,

David