Handle and unify data (different format)

[r_wroblewski]

Dear Community,

I'm working at the moment with data, where some fields are mixed up with 1 byte & 2 byte letters.

So at the end I have graphs and listboxes where same wording appears twice because of different encoding of the raw data.

............

This is just one field which need to be cleaned up and I want to ask if someone already faced this issue and has some tips to unify this data. Until now I just think to create a table with all possibilities and the required result(which should be seen in the application) like

Source	QV
2F	2F
2Ｆ	2F
3F	3F
3Ｆ	3F

and use this to clean up data during loading the script. This will cost a lot of time and also data need to be checked continuously.

Does someone facing the same problem before and has some tips or best practice how to solve this issue?

Best regards,

Ronny

[Gysbert_Wassenaar]

The best solution is to fix the source. Is there any valid reason your source data contains data in multiple encodings? If not perhaps you can get a database administrator to sort this out at the source database.

[r_wroblewski]

Hi Gysbert,

The reason is that the source data is from different countries (partly SHIFT-JIS, partly ANSI/UTF-8) and also in different languages.

Would be great if the client will take care of this issue but I think I also need some plan to solve this issue just in QV.

Best regards,

Ronny

PS: the best would if I could convert directly the letters during loading the data and not words, etc. but not sure how I could achieve this at the moment.

[fvelascog72]

It isn´t the best solution but you can try with MapSubString:

Map:

Mapping LOAD * Inline [

Ｆ, F

];

TMP1:

LOAD

  MapSubString('Map',Field) as "New Field"

Inline [

Field

2F

2Ｆ

3F

3Ｆ

](mac);

(mac) format Field and you have to fill Map table with all posibilities.

I hope it helps you.

[r_wroblewski]

Hi Federico,

Thanks for your solution. At the moment I also can't find a better solution like just cover all possibilities and use a mapping table or new table with cleaned data to solve this issue.

Best regards,

Ronny

PS: If someone has a different approach please share it.