Solved: text data splitting into columns and rows! - Qlik Community

raadwiptec · ‎2015-10-05

Hi all,

could somebody suggest a solution.

the source is a flat file and data is as below

id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule

id=peek secs="2010-03-07 11:36:44" route=9 mk=10 username=sam type=on schedule

id=dest secs="2010-03-07 11:36:44" route=10 mk=10 username=sam type=on schedule

id= arr secs="2010-03-07 11:36:44" route=12 mk=10 username=sam type=on schedule

for example:-

id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule. I am trying split each of them as a column

for ex

id =transit -- so id is a column here and 'transit' is the value

secs="2010-03-07 11:36:44" - so here I want to make 'secs' as the column and 2010-03-07 11:36:44" this is the value and this value may change accordingly with different timestamps and dates in this column

in qlikview if iam loading this I would like to have as below

id ¦ secs ¦

ciaran_mcgowan · ‎2015-10-07

Hi raadwiptec,

Glad to hear it. Subfield works by splitting up a field into smaller pieces by looking through a field for a specified value, once it finds it, it will return everything to the left or right of it.

If you had a string called FRUIT that contained the following vaule: "Apple-Orange-Grape-Pear-Banana" it can be split up using the "-" as a separator like this:

Subfield(FRUIT,'-',1) AS Fruit1, will return Apple

Subfield(FRUIT,'-',2) AS Fruit2 will return Orange

Subfield(FRUIT,'-',3) AS Fruit3, will return Grape

Subfield(FRUIT,'-',4) AS Fruit4, will return Pear

Subfield(FRUIT,'-',5) AS Fruit5, will return Banana

In your case, I used Subfield twice so it will search for everything to the left of the desired text and everything to the right and return what's in between the two:

SubField(SubField(data, 'id=', 2), 'secs=', 1) AS ID

Breakdown:

Original: "id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule"

SubField(data, 'id=', 2) would return everything to the right of the "id=" text

Result: "transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule"

SubField(data, 'secs=', 1) would return everything to the left of the "secs=" text

Result: "id=transit "

Combining the two tells it for first search for everything to the right of "id=" and then, from the result of that, everything to the left of the "secs=" which is why SubField(SubField(data, 'id=', 2), 'secs=', 1) will return transit for the above example.

View solution in original post

raadwiptec · ‎2015-10-05

hi all,

any suggestions here?

ciaran_mcgowan · ‎2015-10-05

You could try loading all values into a single table and doing something like this (in this case the field I load everything into is called "data"):

TableName:
LOAD [@1:n] AS data
FROM
[test.txt]
(fix, codepage is 1252);

CleanData:
LOAD 
SubField(SubField(data, 'id=', 2), 'secs=', 1) AS ID,
SubField(SubField(data, 'secs=', 2), 'route=', 1) AS Secs,
SubField(SubField(data, 'route=', 2), 'username=', 1) AS Route,
SubField(SubField(data, 'username=', 2), 'type=', 1) AS Username,
Subfield(data, 'type=', 2) AS Type
Resident
TableName;

That's assuming that you're loading it using a .txt file

raadwiptec · ‎2015-10-05

its not a text ..its a flat file. I could see the properties as windows shell common

Colin-Albert · ‎2015-10-05

Have a look at the subfield() or textbetween() commands.

ciaran_mcgowan · ‎2015-10-05

Can you send me a version of the file?

raadwiptec · ‎2015-10-05

file version is blank

ciaran_mcgowan · ‎2015-10-05

I don't know what you mean. If you post an example of the file, we might be able to help you more.

raadwiptec · ‎2015-10-06

sorry you were asking me the copy of this file. as mentioned earlier the data looks fully similar like this

id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule

id=peek secs="2010-03-07 11:36:44" route=9 mk=10 username=sam type=on schedule

id=dest secs="2010-03-07 11:36:44" route=10 mk=10 username=sam type=on schedule

id= arr secs="2010-03-07 11:36:44" route=12 mk=10 username=sam type=on schedule

and is more than 15mb I have taken few values. you can consider the same..

and this is a flat file not really a text file...

so I would like the output from the file like below

id should be the column and in the value it should be 'transit'

secs should be the column and in the value it should be 2010-03-07 11:36:44

simililarly...

id ¦ secs ¦ route ¦ mk ¦username

transit

ciaran_mcgowan · ‎2015-10-06

OK, forgot about the text file, that's just what I used to recreate your issue.

Can you load the flat file into Qlikview? If so, load each record into a table. In my example below, I loaded it into a table called TableName. I called each row "data".

I then cleaned it using the below script.

CleanData:

LOAD

SubField(SubField(data, 'id=', 2), 'secs=', 1) AS ID,

SubField(SubField(data, 'secs=', 2), 'route=', 1) AS Secs,

SubField(SubField(data, 'route=', 2), 'username=', 1) AS Route,

SubField(SubField(data, 'username=', 2), 'type=', 1) AS Username,

Subfield(data, 'type=', 2) AS Type

Resident

TableName;

If you do the same, this will separate out each row into the 5 different fields you are looking for and you can create a table using them.