I want to replace a delimiter ('|') from a particular cell from csv file through qlikview.
How is that possible ??
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.
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
(txt, codepage is 1252, no labels, delimiter is '\t', msq)
Where IsNum(subfield(@1, '|', 1))
If( @4<>'', Name & City , Name) AS Name,
If( @4<>'', @4 , City ) AS City
LOAD * INLINE [
(delimiter is '|')
Notice that you will have to append the @4 as an extra embedded label on line 7.
@1 AS Id,
If(@4='',@2,@2&@3) AS Name,
If(@4='',@3,@4) AS City
(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.
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.
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.
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.