topic Re: replace string in QlikView

replace string

Wed, 28 Aug 2013 07:38:11 GMT

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

Re: replace string

MayilVahanan — Wed, 28 Aug 2013 07:47:12 GMT

Try with purgechar() or keepchar() option.

Other Function:

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

Re: replace string

Wed, 28 Aug 2013 07:51:25 GMT

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

Re: replace string

rbecher — Wed, 28 Aug 2013 08:05:42 GMT

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
    &#8217;, '
    &#233;, é
    &#232;, è
    &#39;, '
    &#244;, ô
    &#234;, ê' 
];

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

- Ralf

Re: Re: replace string

rbecher — Wed, 28 Aug 2013 08:18:01 GMT

Maybe better without the space:

MapDia:  
MAPPING LOAD * INLINE [  
    F1, F2  
    &#8217;,'  
    &#233;,é  
    &#232;,è  
    &#39;,'  
    &#244;,ô  
    &#234;,ê   
];

- Ralf

Re: replace string

Wed, 28 Aug 2013 08:36:52 GMT

Of course !

Thanks a lot