Replace delimiter from csv file.

[timanshu]

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.

[timanshu]

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.

[timanshu]

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

[maxgro]

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)) ;