topic Re: Splitting a .CSV file column in to multiple columns in QlikView

Splitting a .CSV file column in to multiple columns

mwscott1 — Tue, 06 Jan 2015 17:04:03 GMT

I have a .csv file with column headings "Name". I need to split the data in the "Name" column in to 3 separate columns with headings CompName, Node, Serial using a space as the delimiter. I also need to remove the .txt if possible. Splitting the data is the main thing.

Sample data

Name

660026-6 660026-6 0KGSA42446.txt

OWNER6-PC 765726DT 1YQNZV1.txt

RCR-CTYPGV1 RL685229A CTYPGV1.txt

Re: Splitting a .CSV file column in to multiple columns

Tue, 06 Jan 2015 17:12:11 GMT

Hi Melvin,

have a look at subfield, something like the below should work for you

Subfield(Name,' ',1) AS CompName,

Subfield(Name,' ',2) AS Node,

Replace(Subfield(Name,' ',3),'.txt',Null()) AS Serial

Edit: forgot you gave the fieldname, just adjusted that

hope that helps

Joe

Re: Splitting a .CSV file column in to multiple columns

Anonymous — Tue, 06 Jan 2015 17:13:40 GMT

You can try this on your load:

SubField(Name,' ', 1) as CompName,

SubField(Name,' ', 2) as Node,

Replace(SubField(Name,' ', 3),'.txt','') as Serial

Regards,

Gabriel

Re: Splitting a .CSV file column in to multiple columns

its_anandrjs — Tue, 06 Jan 2015 17:41:32 GMT

Hi,

You can try this ways also by use Subfield and Where functions

LOAD * Where Serial <> 'txt';
LOAD Name,
SubField(Name,' ',1) as CompName,
SubField(Name,' ',-2) as Node,
SubField( SubField(Name,' ',-1),'.') as Serial;

LOAD * Inline [
Name
660026-6   660026-6 0KGSA42446.txt
OWNER6-PC   765726DT 1YQNZV1.txt
RCR-CTYPGV1   RL685229A CTYPGV1.txt
];

This is example you can use your existing source file in place of inline table

For your table you can try with

LOAD Name,
SubField(Name,' ',1) as CompName,
SubField(Name,' ',-2) as Node,
SubField( SubField(Name,' ',-1),'.') as Serial;
From <Source> Where SubField( SubField(Name,' ',-1),'.') <> 'txt';

Regards

Anand