topic Re: Replace characters in all the data in QlikView

Replace characters in all the data

MindaugasBacius — Wed, 24 Sep 2014 20:06:34 GMT

Hello my dears,

Please help me to replace characters in all the table fields.

Let's say I do have a table (the real table is huge with millions of rows and around 100 columns):

Header1 Header2

AA2A 2DDD

BBB1 EEE1

1CCC FF1F

I need to replace:

1 to 3

2 to 4.

I manage to do it by testing the script:

ReplaceMap:

MAPPING LOAD * INLINE [

char replace

1 3

2 4

] (delimiter is ' ');

TestData:

LOAD

MapSubString('ReplaceMap', Header1) as ReplacedString1;

LOAD

MapSubString('ReplaceMap', Header2) as ReplacedString2;

LOAD * INLINE [

Header1 Header2

AA2A 2DDD

BBB1 EEE1

1CCC FF1F

] (delimiter is ' ');

The result is correct.

But I do wonder if I could optimize the script and remove the loading time. Also, maybe there is a good practise examples of such a task.

Thank you in advance!

Re: Replace characters in all the data

Gysbert_Wassenaar — Thu, 25 Sep 2014 08:54:58 GMT

Best practice is to fix the problem as close as possible to the source. Maybe you can do this in the source database. Your database administrator should be able to come up with a solution using regular expressions and/or a udf. Or

perhaps you can dump the table to a text file first and use a sed or perl script to replace the characters and then read in the corrected text file.

Re: Replace characters in all the data

tobias_klett — Thu, 25 Sep 2014 09:03:59 GMT

Hi,

otherwise you would have to use it for each field like this:

DemoTable:

Load replace(replace(Field1,'1','3'),'2','4') as Field1,....

Re: Replace characters in all the data

MindaugasBacius — Thu, 25 Sep 2014 13:51:30 GMT

I do get your point, Gysbert.

This situation is hardcore and the input files cannot be changed in the way you have suggested.

To be clear, I have found out that the data is in ISO 8859-13 format and should be changed to Windows-1251. I am quite a newbie myself so didn't get it at first sight