Re: Loading unstructured text data - Qlik Community

Anonymous · ‎2017-06-16

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

Anonymous · ‎2017-06-16

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

Anonymous · ‎2017-06-16

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 · ‎2017-06-16

Hi Marc,

see Attachment.

Regards,

Antonio

kamal_sanguri · ‎2017-06-16

I think something like this.

kamal_sanguri · ‎2017-06-16

Pls see attached.

Anonymous · ‎2017-06-16

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 · ‎2017-06-19

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.