topic Re: Convert Plain Text File to Table in QlikView

Convert Plain Text File to Table

Anonymous — Sat, 23 May 2015 08:26:20 GMT

Hi all,

how is it possible to convert a text file of the form:

Rank

Grade

Username

Followers

Channel Views

A++

justin

3,784,184

21,805,586

A++

syndicate

1,880,518

35,578,021

A++

riotgames

1,622,549

703,559,320

A++

captainsparklez

1,135,136

18,164,175

A++

PhantomL0rd

944,461

79,518,626

into a table of the form:

Rank Grade Username Followers Channel Views

1 A++ justin 3,784,184 21,805,586

2 A++ syndicate 1,880,518 35,578,021

3 A++ riotgames 1,622,549 703,559,320

4 A++ captainsparklez 1,135,136 18,164,175

5 A++ PhantomL0rd 944,461 79,518,626

Thanks for the help!

Michael

Re: Convert Plain Text File to Table

Anonymous — Sat, 23 May 2015 18:54:34 GMT

I'm sure there are more effective ways, but here is my exercise attached.

Re: Convert Plain Text File to Table

Anonymous — Sat, 23 May 2015 19:04:56 GMT

A little cleaner version...

Re: Convert Plain Text File to Table

maxgro — Sat, 23 May 2015 22:48:20 GMT

DIRECTORY;

source:

load div(rowno()-1,5)+1 as row, rowno() as id, line;

LOAD @1 as line

FROM Follower.txt

(txt, codepage is 1252, no labels, delimiter is '@', msq);

let s='';

for i=1 to 5

let v$(i)= peek('line', $(i)-1);

let s='$(s)' & chr(10) & 'SubField(line,' & chr(39) & '@' & chr(39) & ', ' & $(i) &') as [' & v$(i) & ']'& if($(i)<>5, ',');

NEXT;

final:

load $(s) ;

load

row, concat(line, '@', id) as line

Resident source

where row <> 1

group by row;

DROP Table source;

Re: Convert Plain Text File to Table

MarcoWedel — Sat, 23 May 2015 23:08:02 GMT

Hi,

one solution could be:

tabInput:
LOAD Div(RecNo()-1,5)+1 as RecNo,
    Mod(RecNo()-1,5)+1 as ColNo,
    [@1:n] as ColVal
FROM [https://community.qlik.com/servlet/JiveServlet/download/781397-166085/Follower.txt] (fix, codepage is 1252);

mapColNames:
Mapping LOAD
  'Col'&ColNo,
  ColVal
Resident tabInput
Where RecNo=1;

tabOutput:
Generic
LOAD RecNo-1 as ID,
    'Col'&ColNo,
  ColVal
Resident tabInput
Where RecNo>1;

DROP Table tabInput;

RENAME Fields using mapColNames;

hope this helps

regards

Marco

Re: Convert Plain Text File to Table

MarcoWedel — Sat, 23 May 2015 23:32:49 GMT

Hi,

loading the file as-is, just with additional column and record numbers, the table view could also be generated using a pivot table:

LOAD Div(RecNo()-1,5)+1 as RecNo,
    Mod(RecNo()-1,5)+1 as ColNo,
    [@1:n] as ColVal
FROM [https://community.qlik.com/servlet/JiveServlet/download/781397-166085/Follower.txt] (fix, codepage is 1252);

hope this helps

regards

Marco