Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner
Partner

Replace characters in all the data

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!

3 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
tobias_klett
Partner
Partner

Hi,

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

DemoTable:

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

MindaugasBacius
Partner
Partner
Author

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