Qlik keeps formatting numbers based on first match

[p_verkooijen]

Got a MSSQL database with numbers formatted with or without a prefix 0

QlikView May 2022SR2 formats the numbers based on sort order. First row is the winner..

Qlik Script:

Output

LEN en TEXT display's the difference in LEN and content

Order by ID ASC all EANcode's become 0850010131146

Order by ID DESC all EANcode's become 850010131146

[Or]

This seems a little silly, but it also doesn't seem too important - you can explicitly set the format you want anyway, so the default shouldn't matter too much? This is also an unsupported version of QlikView (though I imagine the same issue would occur in the newest version). Probably would be best to confirm this with the latest version and open a bug with support if it still happens?

[p_verkooijen]

Hi @Or 

Although it does not seem to be a too important issue based on the example data but it really is. This is not the only occurrence. The default should remain the source format.

Its just not the same number and gives a lot of issues down the road matching values and creating the correct output. Turning the content to TEXT same, exports will be TEXT. 

I see I typed May2021 SR2 instead of May2022 SR2 . (2021 just recently got EOS on May 25, 2023), Tested with May2022SR2, May2023 IR and same issue. Also tested backwards with April2020 and April2019 SR4 all have the same issue output.

Create a Ticket was the next step.

[p_verkooijen]

Another test, did a SQL Select and store as seperate dataset into QVD

Loading QVD's same issue, the first format still wins

[p_verkooijen]

Tested loading these QVD's using Qlik Sense 2023IR

same 😞 

load * from C:\Temp\tmp1.qvd (qvd);
load * from C:\Temp\tmp2.qvd (qvd);

load * from C:\Temp\tmp2.qvd (qvd);
load * from C:\Temp\tmp1.qvd (qvd);

[p_verkooijen]

Created the ticket, for now I fixed it by checking the LEN( vs LEN(NUM(

Because the NUM on load shows the correct length, the prefixed with 0 is 13 and the rest is 12. 

IF(LEN(EANcode)<>LEN(NUM(EANcode)), TEXT(EANcode), EANcode) AS EANcode,

[p_verkooijen]

Seems to be default behavior, see 

https://community.qlik.com/t5/Official-Support-Articles/Leading-zeros-are-missing-in-loaded-data-val...

[ElisaF]

Hi  @p_verkooijen 

Another solution is to use Rob's scripting technique - from the link below:
https://qlikviewcookbook.com/2016/07/touchless-formatting/

In the case of your example, test the script below:

TempFormatTable:
LOAD
TEXT('0') as EANcode
AutoGenerate 1;

Data:
LOAD
ID as ID,
EANcode AS EANcode
from .....
;

DROP Table TempFormatTable;

[p_verkooijen]

@ElisaF thnx but that is not going to work. I want the value to remain 085xxxxx or 85xxxxx

Using you example it will only be 085

As my solution shows, I have to check the length and use TEXT or NUM accordingly to get the full number

[ElisaF]

Hi,

Very strange. I checked the technical solution on QlikView 11.20 SR2 and QlikSense Aug 2023 Patch 3.

The script is as follows:

TempFormatTable:
LOAD
TEXT('0') as EANcode
AutoGenerate 1;

Data:
LOAD
ID                                   AS ID,
EANcode                   AS EANcode,
TEXT(EANcode)     AS EANcode_Text // Desire Output
;
LOAD * Inline [
ID , EANcode
100, 850010131146
101, 0850010131146
102, 0850010131147
103, 850010131147
];

DROP Table TempFormatTable;

Ouput:

If you get the same output with the script above, I recommend in the SQL syntax to convert EANCode to varchar.
Although I don't think this is the cause.