topic Re: Loading text data but converted to numbers from excel in QlikView

Loading text data but converted to numbers from excel

Fri, 25 Oct 2013 00:22:56 GMT

Hi, how are you?

Just a quick question

I am loading a data from excel file (really simple, only 2 columns, product ID and description) as an exercise.

Product.xlsx

ID, // note that for this exercise, this ID label is purposedly made different than the ID label in the database

Product Description

The product ID has a format of "000XXX", meaning that if the product ID is 1, it will be represented as "000001", up to 100. This is only exercise, so the data is not big.

Now I also have a ready QVD data, in which it contains Product ID, and the price.

ProductDatabase.qvd

Product ID, // this product ID has the same format such as "000001"

Price

The objective is really straight forward, that I have to find the price of each product ID that I load from excel, which I have done by using the where exists clause when loading.

However, to my surprise, when I display the ID field in Qlikview, all of the IDs no longer have the leading 0s.

However, if I display the Product ID field, all the IDs still have the leading 0s.

It seems that QV thinks that the IDs loaded from excel as number and therefore removed all the leading 0s whereas it did not do the same thing to the Product IDs loaded from the qvd file.

My question is, is this a bug or the correct behaviour? It seems not consistent? It did however still find the match though, which I am happy.

Re: Loading text data but converted to numbers from excel

jagan — Fri, 25 Oct 2013 02:07:22 GMT

Hi,

It is not a bug. Whenever there are leading 0s qlikview considers it as number and change to normal format by default. If want the numbers as such then you can override this by using Text() to retain 0s like this

LOAD

Text(ProductID AS ProductID,

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Loading text data but converted to numbers from excel

Fri, 25 Oct 2013 04:52:59 GMT

Thank you Jagan,

Yes, it appears to be like that, but I am wondering why is that the product id field that I loaded from the QVD file still has the leading 0s at the front, shouldn't technically they don't have the leading 0s as well?

Re: Loading text data but converted to numbers from excel

jagan — Fri, 25 Oct 2013 05:36:26 GMT

Hi,

May be the qvd has values like that, before saving in qvd you need to convert that to text. Regenerate the QVD again and check. Hope this helps you.

Regards,

Jagan.