How to change region format of a value?

[sculptorlv]

Hello, I have joined to tables from differnet databases. These database have different formats for data:

Latin1_General_CI_AS

Latvian_CI_AS

To join these two database I used Collate function.

The result is shown in a picture bellow:

The quastion is, how can I change the format for the certain column "NAV_Payment_Terms" in order to represent values in normal form without these "squares" (must be identical as in column "TER_Payment_Terms"?

Thank you in advance for help!

[tamilarasu]

Hi Ruslans,

I am not sure whether the boxes are Ascii characters. If it's a trailing space you can try,

Trim (NAV_Payment_Terms)

[sculptorlv]

Doesn't help

[tamilarasu]

Ok. Could you try to remove the trailing in SQL itself.?

Select REPLACE(FieldName,CHAR(13)+CHAR(10),'') From TableName

[sculptorlv]

Can you help me with the syntaxis?

SQL SELECT

  TABLE_NAVISION."Agreement ID" AS Billing_Number,

  TABLE_NAVISION."Invoice Payment Terms" AS NAV_Payment_Terms

FROM "EUR-VENDEN".dbo."EUR VENDEN$Agreement Line" AS TABLE_NAVISION;

[sculptorlv]

Both methods doesn't work... I guess I need to do something on SQL level .. to get TEXT values

[tamilarasu]

I haven't faced these kind of scenario in Qlikview, I guess we can do this Qlikview itself. One last try,

SQL SELECT

  TABLE_NAVISION."Agreement ID" AS Billing_Number,

Replace(Replace ( TABLE_NAVISION."Invoice Payment Terms", Chr(10),'') , Chr(13),'') AS NAV_Payment_Terms

FROM "EUR-VENDEN".dbo."EUR VENDEN$Agreement Line" AS TABLE_NAVISION;

[tamilarasu]

Yes. Seems the text characters are converted to boxes. Check whether you can do something in SQL level.

[jonathandienst]

The boxes are (Latvian?) Unicode characters not supported in the code page used by QV. It seems you need to convert the output in SQL to UTF-8. You should be able to find information in SQL Server forums.

Perhaps the Unicode characters are not supported by the font in use on the server. Changing to a full Unicode font may help.