Übersetzung binärer Werte

[timo_brenner]

Hello everybody,

we are working with Microsoft Dynamics NAV. A few fields have the data type "DateFormula".

If we are loading them in Qlik sense, the values are not shown correctly.

For example, the field "Loskumulierungsperiode" has values 5M, 3D, 7W,... 

The letter represents a period (M = Month, D =Days, W = Weeks). 

In Qlik sense the letters are not visible, instead it looks like this:

We found out, that NAV saves the Values as binary values.

How can we shown  the values correctly?

Best regards

Timo 

[marcus_sommer · Accepted solution]

You may separate the information and then checking the content, maybe with something like:

keepchar(Field, '0123456789') as PeriodNumber,
purgechar(Field, '0123456789') as PeriodUnit

and then:

len(PeriodUnit) as LenPeriodUnit,
ord(PeriodUnit) as OrdPeriodUnit

If the length is really > 1 you would need something like mid(PeriodUnit, n, 1) to pick all single chars. If you could really identify a different  contents from OrdPeriodUnit you could query and/or map them with the appropriate matching-values. If there are no different values it means that the driver didn't transfer them properly and you may need to switch to another driver and/or to configure them in any way and/or you apply already an appropriate converting by querying the database.

- Marcus 

[timo_brenner]

Hi Marcus,

thank you for your advice.

The Char-function worked but I had to do it in the SQL-statement.

Best regards,

Timo