topic Replace delimiter from csv file. in QlikView

Replace delimiter from csv file.

timanshu — Sun, 08 Mar 2015 12:48:10 GMT

Hi,

I want to replace a delimiter ('|') from a particular cell from csv file through qlikview.

How is that possible ??

Example :

Id|Name|City

1|Abc|Banglore

2|C|de|Chennai

3|Fgh|Delhi

For Id '2', Name should be 'Cde' and City as 'Chennai' . But Qv will take Name as 'C' and City as 'de'.

How to solve this issue through Qlikview.?? without changing Source file.

Re: Replace delimiter from csv file.

maxgro — Sun, 08 Mar 2015 13:38:31 GMT

LOAD

subfield(@1, '|', 1) as Id,

if(subfield(@1, '|', 1)=2, subfield(@1, '|', 2) & subfield(@1, '|', 3), subfield(@1, '|', 2)) as Name,

if(subfield(@1, '|', 1)=2, subfield(@1, '|', 4), subfield(@1, '|', 3)) as City

FROM yourfile.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq)

Where IsNum(subfield(@1, '|', 1))

;

Re: Replace delimiter from csv file.

petter — Sun, 08 Mar 2015 14:21:04 GMT

LOAD

Id,

If( @4<>'', Name & City , Name) AS Name,

If( @4<>'', @4 , City ) AS City

;

LOAD * INLINE [

Id|Name|City|@4

1|Abc|Banglore

2|C|de|Chennai

3|Fgh|Delhi

]

(delimiter is '|')

;

Notice that you will have to append the @4 as an extra embedded label on line 7.

Re: Replace delimiter from csv file.

petter — Sun, 08 Mar 2015 14:53:35 GMT

LOAD

@1 AS Id,

    If(@4='',@2,@2&@3) AS Name,

    If(@4='',@3,@4) AS City

;

LOAD @1,

     @2,

     @3,

     @4

FROM

[FixVariableDelims.txt]

(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 1 lines);

This is a better version than the previous one I submitted - because that had INLINE with an appended header column which will not work for an external file. This does however.

Re: Replace delimiter from csv file.

petter — Sun, 08 Mar 2015 14:54:06 GMT

Flawed solution ... actually - if you want to not load from inline ...

Re: Replace delimiter from csv file.

timanshu — Sun, 08 Mar 2015 16:02:04 GMT

Hi Massimo,

Thanx for reply.

When we defined the delimiter then subfield(@1, '|', 2) will give null as delimiter ('|') is not present in @1 field , since it separated fields.

Even we could have find solution by concatenating next field value into current one to get the result but that would be very complex as in actual I have more than 20 fields.

Re: Replace delimiter from csv file.

timanshu — Sun, 08 Mar 2015 16:05:18 GMT

Hi petter,

Thanks for your reply.

I was actually looking for some solution that does not require to write so much of if statement for as I have more than 20 fields in actual.

Re: Replace delimiter from csv file.

petter — Sun, 08 Mar 2015 16:16:16 GMT

OK - then help us understand your real requirements then by specifying if there is a consistent logic in how the different fields interrelate...

Re: Replace delimiter from csv file.

petter — Sun, 08 Mar 2015 16:27:20 GMT

If you upload a sample of your CSV-file it would be so much easier for us to help you

Re: Replace delimiter from csv file.

maxgro — Sun, 08 Mar 2015 16:55:40 GMT

I think you have to first load the data without | delimiter (as in my answer) and then, with some logic, calculate the fields or replace some of the | with null. If you can, post some rows of your real data and someone will try to give a better answer.

Re: Replace delimiter from csv file.

timanshu — Sun, 08 Mar 2015 17:41:34 GMT

Hi Massimo,

I cant upload data. It's confidential. You can just think of the same scenario as in my example plus consider there are more fields in the data source. And one field in a row contains delimiter between the field value as text. rest columns contain correct data. So data shifts to next field and so on.

Re: Replace delimiter from csv file.

timanshu — Sun, 08 Mar 2015 17:43:46 GMT

If you could give solution with one of your idea as you said uploading data as plain text, it would be great.

Re: Replace delimiter from csv file.

maxgro — Sun, 08 Mar 2015 18:01:49 GMT

LOAD

subfield(Row, '|', 1) as Id,

subfield(Row, '|', 2) as Name,

subfield(Row, '|', 3) as City

;

// load all '|' separated fields in one field (Row)

// when there is 3 '|', drop the second '|' so all rows have the same number of '|', 2

// I think you only have row of n or n+1 '|'

// and the '|' to remove is always in the same position

LOAD

if(Index(@1, '|', 3)>0,

left(@1, Index(@1, '|', 2)-1) & Right(@1, len(@1) - Index(@1, '|', 2)),

@1) as Row

FROM a.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq)

Where IsNum(subfield(@1, '|', 1)) ;