Qlik12 wrongly takes the text code as a number when Qlik11.2 doesn't

[francisco_marti]

Hi.  

     We are trying to upgrade our Qlik version from 11.2 to 12 but we came up with a big problem of figures changed after our first upgrade testing.

     We finally figured out that the problem become from our article master code. This field is a text but when Qlik 12 retrieved it from our SAP database, in particular from MARA.MATNR CHAR(18) field, Qlik12 trasnformed about 20 text codes into a number format.

     We know we can do a workaround putting first TEXT() to convert, or to force text format to that field, but we would want to know why is this happening? specially because it was a specific text code with a letter T in the middle. May be Qlik is understanding it as a mathematic notation, isn't it?

An example of our wrong changed codes is this:

00110110T000100

00110110T000200

01010326T000100

01010326T000200

01011105T000100

05501019T000100

05501019T000200

05501019T000210

05501019T000212

05501019T000213

05501020T000100

05501020T000110

05501103T000100

06091013T000100

06091013T000200

which Qlik transformed into this:

-492.783,99931
-689.931,99931
-689.931,99861
-656.984,99931
-656.984,99861
-656.760,99931
-492.783,99861
-492.783,99850
-492.783,99847
-492.783,99846
-492.782,99931
-492.782,99919
-492.768,99931
-471.240,99931
-471.240,99861

Has anyone been in a similar situation?

Thank you in advanced.

[atoz1158]

Hi Francisco

It looks like those entries are being treated as if they are datetime strings which are normally DDMMYYYThhmmss

Regards

Adrian

[Anonymous]

I know that "problem" with numbers like 01E0  or 01D0

but not with "T"

perhaps try

Set ExponentNumberNotation = 0;

But you should know what you're doing at that point.

[Anonymous]

just tried it, doesn't work for numbers containing "T"

[francisco_marti]

Yes, you are right. We are going to make a

TEXT()

in order to ensure we are getting the correct data.

TEXT() is the important one and it is recommended to do it at the first extraction from the data base, not later.

Thank you.

[Colin-Albert · Accepted solution]

ISO 8601 specifies that <date>T<time> is one option for formatting timestamps, so Qlik has conveerted data that matches this to a timestamp in the load script, and stored the value as a decimal timestamp.