Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

marc_verhelst
New Contributor

Loading unstructured text data

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

7 Replies
mattquinnterex
Contributor II

Re: Loading unstructured text data

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

marc_verhelst
New Contributor

Re: Loading unstructured text data

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

antoniotiman
Honored Contributor III

Re: Loading unstructured text data

Hi Marc,

see Attachment.

Regards,

Antonio

kamal_sanguri
Valued Contributor

Re: Loading unstructured text data

I think something like this.

Capture.PNG

kamal_sanguri
Valued Contributor

Re: Loading unstructured text data

Pls see attached.

marc_verhelst
New Contributor

Re: Loading unstructured text data

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

kamal_sanguri
Valued Contributor

Re: Loading unstructured text data

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.

Community Browser