topic Re: Load multiple lines as a free-text field in QlikView

Load multiple lines as a free-text field

shane_spencer — Mon, 17 Nov 2014 14:14:51 GMT

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?

Re: Load multiple lines as a free-text field

Peter_Cammaert — Mon, 17 Nov 2014 14:45:08 GMT

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

Re: Load multiple lines as a free-text field

shane_spencer — Mon, 17 Nov 2014 15:03:23 GMT

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.

Re: Load multiple lines as a free-text field

Peter_Cammaert — Mon, 17 Nov 2014 15:18:05 GMT

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...

Re: Load multiple lines as a free-text field

perumal_41 — Mon, 17 Nov 2014 17:33:13 GMT

PFA,I hope it help to you

Re: Load multiple lines as a free-text field

shane_spencer — Tue, 18 Nov 2014 08:33:38 GMT

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"?

Re: Load multiple lines as a free-text field

perumal_41 — Tue, 18 Nov 2014 09:06:45 GMT

Hi,

Please paste sample output how you want ?

Re: Load multiple lines as a free-text field

Peter_Cammaert — Tue, 18 Nov 2014 09:08:41 GMT

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?

Re: Load multiple lines as a free-text field

shane_spencer — Tue, 18 Nov 2014 09:42:26 GMT

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.

Re: Load multiple lines as a free-text field

daveamz — Tue, 18 Nov 2014 12:36:41 GMT

Hi Shane,

See the script attached.

Regards,

David

Re: Load multiple lines as a free-text field

shane_spencer — Tue, 18 Nov 2014 13:44:17 GMT

Hi David, it looks like some of the Statement text is missing:

Re: Re: Load multiple lines as a free-text field

daveamz — Tue, 18 Nov 2014 15:00:11 GMT

I guess I figured out now. It was missing because of the text delimiter =.

Re: Re: Re: Load multiple lines as a free-text field

shane_spencer — Wed, 19 Nov 2014 10:30:44 GMT

Thanks David - that's great. I'm just trying to understand the scripting and have a question about this:

TextBetween(@1, '=','')

That's obviously returning the text of '@1' between the Equal Sign '=' and ''...

...does the 2 single quotes '' simply mean end of the line?

Re: Re: Re: Re: Load multiple lines as a free-text field

shane_spencer — Fri, 21 Nov 2014 13:20:09 GMT

Unfortunately when I ran Log-clean.qvw against my entire log file I found it didn't concat the rows in the correct order. For example the following Statement text:

= SELECT COUNT(*) FROM (SELECT * FROM V_GENERIC_CASES_NOTES WHERE Classification = 'Enquiry/ Orders'

and (CASE_OVERVIEW LIKE

'%CHARITY COMMISSION ORDER%'

or CASE_OVERVIEW LIKE

'%Charity Commision Order%'

or CASE_OVERVIEW LIKE

'%Charity commision order%'

or CASE_OVERVIEW LIKE

'%charity commision order%'

) AND CREATED_DATE >= ? AND CREATED_DATE <= ? AND ((ORGUNIT_ID IN (1063,1083,1084,1085,1173,1240,1252))) FETCH FIRST 15000 ROWS ONLY ) countTableAlias

Got rebuilt as:

SELECT COUNT(*) FROM (SELECT * FROM V_GENERIC_CASES_NOTES WHERE Classification = 'Enquiry/ Orders' '%CHARITY COMMISSION ORDER%' '%Charity Commision Order%' '%Charity commision order%' '%charity commision order%' ) AND CREATED_DATE >= ? AND CREATED_DATE <= ? AND ((ORGUNIT_ID IN (1063,1083,1084,1085,1173,1240,1252))) FETCH FIRST 15000 ROWS ONLY ) countTableAlias and (CASE_OVERVIEW LIKE or CASE_OVERVIEW LIKE or CASE_OVERVIEW LIKE or CASE_OVERVIEW LIKE

Therefore I combined the two scripts from daveamz01 and to perumal_41 getting a working solution.

Re: Re: Re: Re: Load multiple lines as a free-text field

shane_spencer — Fri, 21 Nov 2014 14:43:53 GMT

I've just figured out that all I needed to do was add the sort-weight to the concat:

Re: Re: Re: Re: Re: Load multiple lines as a free-text field

perumal_41 — Fri, 21 Nov 2014 17:15:37 GMT

Hi,

PFA

Re: Load multiple lines as a free-text field

daveamz — Mon, 24 Nov 2014 07:27:02 GMT

Yes, I used '' for end of the line. It seems qlikview can't handle '\r\n' (end of line for Windows text file).