topic Re: Qlik keeps formatting numbers based on first match in Connectivity & Data Prep

Qlik keeps formatting numbers based on first match

p_verkooijen — Thu, 15 Jun 2023 13:12:26 GMT

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

Re: Qlik keeps formatting numbers based on first match

Or — Thu, 15 Jun 2023 12:44:11 GMT

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?

Re: Qlik keeps formatting numbers based on first match

p_verkooijen — Thu, 15 Jun 2023 13:05:01 GMT

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.

Re: Qlik keeps formatting numbers based on first match

p_verkooijen — Thu, 15 Jun 2023 13:35:11 GMT

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

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

Re: Qlik keeps formatting numbers based on first match

p_verkooijen — Thu, 15 Jun 2023 13:39:30 GMT

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);

Re: Qlik keeps formatting numbers based on first match

p_verkooijen — Thu, 15 Jun 2023 14:58:38 GMT

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,

Re: Qlik keeps formatting numbers based on first match

p_verkooijen — Mon, 19 Jun 2023 07:19:55 GMT

Seems to be default behavior, see

https://community.qlik.com/t5/Official-Support-Articles/Leading-zeros-are-missing-in-loaded-data-values/tac-p/2085136#M9473

Re: Qlik keeps formatting numbers based on first match

ElisaF — Mon, 30 Oct 2023 12:42:33 GMT

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;

Re: Qlik keeps formatting numbers based on first match

p_verkooijen — Mon, 30 Oct 2023 12:56:00 GMT

@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

Re: Qlik keeps formatting numbers based on first match

ElisaF — Mon, 30 Oct 2023 13:50:51 GMT

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.

Re: Qlik keeps formatting numbers based on first match

p_verkooijen — Tue, 31 Oct 2023 14:30:17 GMT

Hi @ElisaF ,

Thank you for the example, I will check if this works for me

Re: Qlik keeps formatting numbers based on first match

ElisaF — Wed, 01 Nov 2023 03:59:52 GMT

Thanks for the reply. I look forward to your feedback. I am curious if the solution solved your issue.