Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
r_wroblewski
Partner - Creator III
Partner - Creator III

Handle and unify data (different format)

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.

SnapShot.PNG............

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

SourceQV
2F2F
2F2F
3F3F
3F3F

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

4 Replies
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.


talk is cheap, supply exceeds demand
r_wroblewski
Partner - Creator III
Partner - Creator III
Author

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
Partner - Specialist
Partner - Specialist

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.

r_wroblewski
Partner - Creator III
Partner - Creator III
Author

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.