replace string

[#1]

Hello

In the ETL part, I'm getting string from a database

Unfortunately some had the bad idea to encode diacritic as html in the database

so in the sql select I'm doing such tricky thing to make replacement

replace(replace(replace(replace(Replace(Replace(detdsc.discrepancyreason,'’',''''),'é','é'),'è','è'),''',''''),'ô','ô'),'ê','ê') as discrepancyreason,

Is there some easiest way to do that in Qlikview ?

Thanks for your help

[MayilVahanan]

Hi

Try with purgechar() or keepchar() option.

Other Function:

TextBetween() , mid(), index() function also there, it depends on your requirement.

[#3]

Thank you

Unfortunately it seems that the function you are proposing will be even much more tricky than the replace I'm doing in SQL mode

[rbecher · Accepted solution]

Hi Oliver,

I think you can just copy the replace expression and use it in Qlikview LOAD. Another efficient way is to use MapSubstring() function:

MapDia:
MAPPING LOAD * INLINE [
    F1, F2
    ’, '
    é, é
    è, è
    ', '
    ô, ô
    ê, ê' 
];

Data:
LOAD MapSubString('MapDia', discrepancyreason) as discrepancyreason, ...

- Ralf

[rbecher]

Maybe better without the space:

MapDia:  
MAPPING LOAD * INLINE [  
    F1, F2  
    ’,'  
    é,é  
    è,è  
    ','  
    ô,ô  
    ê,ê   
];

- Ralf

[#6]

Of course !

Thanks a lot