topic Re: Need help to load flat files in QlikView

Need help to load flat files

Tue, 21 Jul 2015 08:36:46 GMT

I am getting data in flat file in the attached format so that data will come either in these two formats

Wed Jul 15 10:56:36 EDT 2015

Usage – 100%

and

Wed Jul 15 11:06:15 EDT 2015

The system ABCD down for 5 minutes

ABCD Usage – 99.99%

in the load script i want to load the timestamp , System name , duaration and usage % as following

Timestamp	System	Duration	Usage %
Jul 15 11:06:15 EDT 2015	ABCD	5	99.99
Jul 15 10:56:36 EDT 2015	ABCD	0	100

Re: Need help to load flat files

Ralf-Narfeldt — Tue, 21 Jul 2015 08:43:45 GMT

In the first message, ABCD is not included. Is this to be assumed for all entries, or do you have other possible System?

Re: Need help to load flat files

Tue, 21 Jul 2015 08:50:18 GMT

It should be assumed please refer the expected data xls which i have attached just now

Re: Need help to load flat files

Ralf-Narfeldt — Tue, 21 Jul 2015 09:32:26 GMT

First step is to load the raw data as fix files as that can manage data that spans several lines easily. They will concatenate into one table.

Each record will contain one log item of several lines, including CR characters (Chr(13)).

You will of course need to adjust the path:

Directory;

Logs:

LOAD [@1:n] As Log

FROM

[..\Downloads\Sample1.txt]

(fix, codepage is 1252, record is 3 lines);

LOAD [@1:n] As Log

FROM

[..\Downloads\Sample2.txt]

(fix, codepage is 1252, record is 3 lines);

Then you need to parse the data into a new table.

Parsed:

Load

Mid(Log, 5, Index(Log, Chr(13)) - 5) As Timestamp,

'ABCD' As System,

PurgeChar(Subfield(Log, 'Usage', 2), '% –') As Usage%,

If(Index(Log,'minutes'),Subfield(SubField(Log, 'used for ', 2), ' minutes', 1), 0) As Duration

resident Logs;

finally, drop the raw table

drop table Logs;

Re: Need help to load flat files

Tue, 21 Jul 2015 13:00:20 GMT

thanks i checked but it is not giving the timestamop

Re: Need help to load flat files

Ralf-Narfeldt — Tue, 21 Jul 2015 13:24:00 GMT

This is the result I get when loading the script using the sample files you provided.

Did you try with the same sample files?

I did notice that there is a discrepancy between the sample written in your question, and the actual sample file you uploaded.

In the question it says "The system ABCD down for 5 minutes" while the sample file says "The system ABCD used for 5 minutes"

Re: Need help to load flat files

Tue, 21 Jul 2015 13:42:01 GMT

Yes you are right thanks

Re: Need help to load flat files

qlikviewwizard — Tue, 21 Jul 2015 13:50:02 GMT

Hi binodkumar_22

If you get the solution, please close the thread. Thank you.

Re: Need help to load flat files

Ralf-Narfeldt — Tue, 21 Jul 2015 13:50:46 GMT

Great! Please mark as Correct if you are satisfied with the answer!