Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
2F | 2F |
3F | 3F |
3F | 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
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.
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.
It isn´t the best solution but you can try with MapSubString:
Map:
Mapping LOAD * Inline [
F, F
];
TMP1:
LOAD
MapSubString('Map',Field) as "New Field"
Inline [
Field
2F
2F
3F
3F
](mac);
(mac) format Field and you have to fill Map table with all posibilities.
I hope it helps you.
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.