topic Re: Extract text from long string in QlikView

Extract text from long string

djbloiss — Tue, 31 Jul 2018 13:02:02 GMT

I am working on a QVD file to analyze NPrint logs. I want something more detailed than any I have found. The first thing I am trying to do is pull the task names into its own field.

This is what I am trying:

LET rowText = NoOfRows('tmpLogs'); // get the total number of rows in Timeline table

for i=1 to $(rowText) // loop through every row

let CurrMessage = FieldValue(Message,$(i)); //get the value for "text" field on each row

let PrevMessage = FieldValue(Message,$(i)-1);

LogData:

LOAD

if(WildMatch($(CurrMessage), '*Run Task*'), mid($(CurrMessage),Index($(CurrMessage),'Run Task:')+1)) as TaskName

resident tmpLogs;

It is crashing on the Wildmatch line. What am I missing? It is saying Error in Expression: ')' expected but I cannot see any missing parenthesis in the line.

Re: Extract text from long string

sunny_talwar — Tue, 31 Jul 2018 13:03:40 GMT

May be add single quotes around the variable

LET rowText = NoOfRows('tmpLogs'); // get the total number of rows in Timeline table

for i=1 to $(rowText) // loop through every row

let CurrMessage = FieldValue(Message,$(i)); //get the value for "text" field on each row

let PrevMessage = FieldValue(Message,$(i)-1);

LogData:

LOAD

if(WildMatch('$(CurrMessage)', '*Run Task*'), mid('$(CurrMessage)',Index('$(CurrMessage)','Run Task:')+1)) as TaskName

resident tmpLogs;

next

Re: Extract text from long string

djbloiss — Tue, 31 Jul 2018 13:46:39 GMT

Sunny,

now it is not longer crashing but it is not returning any values. The Syntax looks correct and it is generating no errors. Have any ideas? This should work on any Nprint log file.

Re: Extract text from long string

sunny_talwar — Tue, 31 Jul 2018 13:51:34 GMT

Would you be able to give 2-3 rows of lines to check this out?

Re: Extract text from long string

djbloiss — Tue, 31 Jul 2018 14:02:18 GMT

Application 7/23/2018 11:33:42 AM Message Run Task: Execute Sales Divisions Daily Update SchoolsApplication 7/23/2018 11:33:42 AM Message Run Task: Execute Sales Divisions Daily Update Schools

Application 7/23/2018 11:34:11 AM Message Open file: \\172.16.0.28\d$\Production\Max_Quality_Food_Data_Model_NewSource.qvw

Application 7/23/2018 11:35:03 AM Information QlikView version: 11.20.12451.0

Application 7/23/2018 11:35:04 AM Message Clear all selections: \\172.16.0.28\d$\Production\Max_Quality_Food_Data_Model_NewSource.qvw

Every line starts with Application

Re: Extract text from long string

sunny_talwar — Tue, 31 Jul 2018 14:11:13 GMT

Got it... checking

Re: Extract text from long string

sunny_talwar — Tue, 31 Jul 2018 14:17:54 GMT

Can you see if this works?

Table:

LOAD @1,

If(SubStringCount(@1, 'Run Task') > 1, SubField(@1, 'Run Task:', -1)) as TaskName

FROM

[..\..\Downloads\309546.txt]

(txt, codepage is 1252, explicit labels, delimiter is ';', msq);