topic Re: Put unstructured text in columns in QlikView

Put unstructured text in columns

Fri, 26 May 2017 11:28:13 GMT

Hi,

I have data in the following format in one of the columns in my table

Area : CXX
Application : Happy
Spot : xyz42i
Component : xyz42i
Summary xyz42i - The value of Avg_Response_Time for /D:/Docs/ABDCD/WASparty2 is 1.54 seconds

I need to put all this information in different columns to eavluate the data. The Summary bit also needs to be sliced to give groups Like Average response time, time in queues, address of component etc. The length and format of summary are variable and it is a large file. Any suggestions on how to approach this problem?

Many thanks!

Re: Put unstructured text in columns

rwunderlich — Fri, 26 May 2017 17:15:51 GMT

Piece of cake.

1. Split the lines into individual rows using Subtring(Input, chr(10))

2. Use a Generic Load to transform the "fieldname : value" pairs into fields.

3. The SummaryMetrics load below gives an example of parsing the summary string. You indicated there may be more metrics in the Summary, you could build out the example.

Input:

Directory;

LOAD RecId,

subField(Raw, chr(10)) as Element

FROM

RawData.xlsx

(ooxml, embedded labels, table is Sheet1);

Fields:

Generic

LOAD

RecId,

Trim(SubField(Element, ':', 1)),

Trim(SubField(Element, ':', 2))

Resident Input

Where NOT Element LIKE 'Summary*'

;

SummaryMetrics:

LOAD

RecId,

TextBetween(Element, 'is ', 'seconds') as AvgResp

Resident Input

Where Element LIKE 'Summary*Avg_Response_Time*'

;

DROP TABLE Input;

RecId	Spot	Component	Area	Application	AvgResp
1	xyz42i	xyz42i	CXX	Happy	1.54

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Put unstructured text in columns

felipedl — Fri, 26 May 2017 17:21:38 GMT

Cool way to do it Rob.

Makes it really easy and clean to do it.

Re: Put unstructured text in columns

Thu, 01 Jun 2017 08:28:39 GMT

Hi Rob,

Many thanks for your help. I tried using it and it does give me fields like in the picture. But how should I get them to show up in the main sheet? Sorry I am very new to this.

Re: Put unstructured text in columns

rwunderlich — Mon, 05 Jun 2017 23:23:28 GMT

That is the table model you are posting. To view the data on a sheet, you will need to create a visualization such as a table on the sheet. Try adding a few of the fields to a Table object on the sheet.

-Rob