topic Re: Loading unstructured text data in QlikView

Loading unstructured text data

Anonymous — Fri, 16 Jun 2017 07:07:30 GMT

Purpose is analysing log file of FLEXLM license manager.

Wants to load text file where on some points in file, there is a date. From that point, date must be changed, till I find a new date in file :

fex. :

6:06:27 (lmgrd) TIMESTAMP 6/12/2017

6:06:27 (ptc_d) OUT: "A" 1@PCDELL11593W7

7:00:40 (ptc_d) IN: "A" 2@PCDELL13306 (INACTIVE)

7:04:16 (ptc_d) OUT: "B" 2@PCDELL13306

7:19:27 (ptc_d) OUT: "B" 3@PCDELL13295

6:06:27 (lmgrd) TIMESTAMP 6/13/2017

6:06:27 (ptc_d) OUT: "A" 1@PCDELL11593W7

7:00:40 (ptc_d) IN: "A" 2@PCDELL13306 (INACTIVE)

7:04:16 (ptc_d) OUT: "B" 2@PCDELL13306

7:19:27 (ptc_d) OUT: "B" 3@PCDELL13295

In my result, I want to see the date find in timestamp added to the time in the following lines. How can this be done during load of text file.

Result should be

6/12/2017 6:06:27 (ptc_d) OUT: "A" 1@PCDELL11593W7

6/12/2017 7:00:40 (ptc_d) IN: "A" 2@PCDELL13306 (INACTIVE)

6/12/2017 7:04:16 (ptc_d) OUT: "B" 2@PCDELL13306

6/12/2017 7:19:27 (ptc_d) OUT: "B" 3@PCDELL13295

7/12/2017 6:06:27 (ptc_d) OUT: "A" 1@PCDELL11593W7

7/12/2017 7:00:40 (ptc_d) IN: "A" 2@PCDELL13306 (INACTIVE)

7/12/2017 7:04:16 (ptc_d) OUT: "B" 2@PCDELL13306

7/12/2017 7:19:27 (ptc_d) OUT: "B" 3@PCDELL13295

Re: Loading unstructured text data

Anonymous — Fri, 16 Jun 2017 08:27:36 GMT

As long as the time is actually being stored within the source file in the first place, then you can use the Timestamp Function.

Timestamp(yourDate, 'DD/MM/YYYY h:mm:ss) as yourDate

'yourDate' would be the name of the date column.

Hope this helps

Re: Loading unstructured text data

Anonymous — Fri, 16 Jun 2017 09:18:39 GMT

Matthew,

Problem is not some sort of concatenating, but how can the date be kept in memory during load.

During load :

@1 = 6:06:27

@2 = (lmgrd)

@3 = Timestamp

@4 = 6/12/2017.

In second line is @4 : "A"

This means that 6/12/2017 is disappeared for that line.

How can 6/12/2017 been kept in memory and added to every line till new date (6/13/2017) appears. From then that new date has to be in memory till next one.

Regards,

Marc

Van: Matthew Quinn

Verzonden: vrijdag 16 juni 2017 10:28

Aan: VERHELST Marc <marc.verhelst@vanhool.be>

Onderwerp: Re: - Loading unstructured text data

Re: Loading unstructured text data

antoniotiman — Fri, 16 Jun 2017 09:27:05 GMT

Hi Marc,

see Attachment.

Regards,

Antonio

Re: Loading unstructured text data

kamal_sanguri — Fri, 16 Jun 2017 09:27:25 GMT

I think something like this.

Re: Loading unstructured text data

kamal_sanguri — Fri, 16 Jun 2017 09:28:00 GMT

Pls see attached.

Re: Loading unstructured text data

Anonymous — Fri, 16 Jun 2017 09:49:15 GMT

Kamal,

Thanks for help.

See that first statement is inline.

Data is coming from .txt file that I’m reading in (With a lot of other garbage in it).

I thought that inline is not compatible with reading the data at that moment from a file. So can reading from file and inline at the same moment bringing together.

Regards,

Marc

Van: Kamal Kumar Sanguri

Verzonden: vrijdag 16 juni 2017 11:29

Aan: VERHELST Marc <marc.verhelst@vanhool.be>

Onderwerp: Re: - Loading unstructured text data

Re: Loading unstructured text data

kamal_sanguri — Mon, 19 Jun 2017 05:21:27 GMT

Hi Marc,

I used inline just for the demo purpose, In fact data is treated in same way irrespective of it's source type.

Pls find attached sample where I have used same data in text file. As you mentioned their is lot of garbage in your text file you may need to put some more effort to clean the data.