Auto Removal of Leading Zeros

[Syahfira1]

Hello 🤗

I am facing an issue with leading zeros in Qlik Sense.

My source data is coming from an Excel file.

One of the column in the data is called Partner Code. Some values in the column are only has numeric, some are alphanumeric. 

Refer image below.

The leading zeros on the numeric values are auto removed when loaded into Qlik. 

For example,

• 0000000000 becomes 0
• 0006084270 becomes 6084270

Issue is my source data contains single 0 and 0000000000 value. When the leading zeros is removed, 

• 0 becomes 0
• 0000000000 becomes 0

Hence, both number become similar now but in real, both are different number without the auto removal of leading zeros.

I have tried to text the Partner Code column. Another issue is raised when the single 0 becomes 0000000000 after texted.

I don't know why the single 0 becomes 0000000000 after texted. I have checked the length of the Partner Code.

• For 0 = Len is 1
• For 0000000000 = Len is 10

Could you help me on how I can fix this issue auto removal of leading zeros issue?

Thank you.

🌻

[sandeep-singh · Accepted solution]

This is the fundamental behavior of the Qlik engine. If you want to fix this then you have made updates in the load script instead of UI side. Below is the Qlik article for better understanding. It hopes it will resolve your issue.

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

[Syahfira1]

Hi @sandeep-singh ,

Thank you very much for the reply. I have read the link given. It is helpful for me to understand better on the behavior of Qlik.

As mentioned in my post, I have tried to fix the issue by texting the Partner Code TEXT(Partner Code).

It is the same advice given on the link to use TEXT to fix the issue too.

But my issue is not fixed even after using TEXT function.

I have tried to text the Partner Code column. Another issue is raised when the single 0 becomes 0000000000 after texted.

I don't know why the single 0 becomes 0000000000 after texted. I have checked the length of the Partner Code.

Is there a way to fix this issue ?

[avinashelite]

Will you be able to share the sample data for us to check 

[sandeep-singh]

Hi @Syahfira1,

Did you try fixing it at the UI end? Could you share me your script with sample data to  validate?

[Syahfira1 · Accepted solution]

@sandeep-singh & @avinashelite 

Hi. Thank you very much for your help and respond to my question.

@sandeep-singh , your answer is correct. TEXT syntax is the right way to encounter the zeros issue and which I have done that before.

I have found the real issue on why the TEXT is not working for my case.

It is because I load the Excel file from Online SharePoint thru specific codes (codes to fetch files from SharePoint) in the load editor but not thru SharePoint Connector in the Data Connection.

My company doesn't create a SharePoint Data Connection due to the permission issue with SharePoint.

Hence, they have provide us a solution by giving few lines of codes to run in order to load the files from Online SharePoint.

So, the codes will load the file and automatically become a table. Hence, if I need to do any changes to the columns, I need to resident the table loaded.

That is the reason why the TEXT is not working because I'm TEXTing on a resident table and not the original loaded table.

The real cause is found and managed to fix it by reading the files from Shared Folder directly instead of SharePoint.

Thank you.