Format of data mess up the keyfield

[#1]

Hi everyone,

I got a problem about data format. I use Sedol as the key from two source one from excel and one from tsv.

In both source, the data value is , let say, 12345. But the one from excel become 12345.00000, and the one from tsv file is still 12345. As a results, when I do the left join, qlikview assume there is no match.....

Can anyone help?

Regards,

Ivan

[#2]

Hi ivanyeung,

I hope it is a .csv file and not a .tsv file. Anyways, if you format the data comming into QlikView, you will be able to solve your issue. What you must do is, while loading the data into your QV application, just use the num() function or the num#() function to make the required field to number format. You will find these functions in the reference manual. Just go through it before using it. I hope this must help.

eg:

Load num(Value) as Value,

.............................

from abc.xls;

Hope this helps you.

Thanks

Joseph

[#3]

Thanks Joseph,

I have tried what you sugggested, but QV simply assume it is a mistake.

For example, the value in excel is 1234. Excel define it is a number. without using num(), I get 1234.00000 as string. If I use num(), I can't the value of beither 1234 nor 1234.00000 are not existing.

reagrds,

Ivan

[#4 · Accepted solution]

Hi Ivanyeung,

I am attaching a qlikview application along with this post. I tried to load data from an excel sheet and it is automatically taking the values as nymbers. I suggest that you format the excel cells by right clicking on the specific field and format cells to numbers or integers. Try this . hope this helps you.

Thanks Joseph....

[#5]

Hi Joseph,

Thanks for your files. To clarify thing a bit further here:

Let say 1234 is the common key value bewteen 2 table. The problem here are:

1. I have use the cross table function;

2. one 1234 is data (number in Qlikview), another one (different excel file) is attribute(string in Qlikview)

regards,

Ivan

[#6]

Hi Ivnyeung,

I have also come through the following problem before. you cannot chage the format of the value you get after doing a cross table. It will remail as a string itself. So what you can do is you can chage the format of the files coming from the excel sheet to string. By this way you get both the fields in the string form at and by doing this you will be able to solve your issue. Hope this helps you.

Thanks joseph......

[#7]

Hi Joseph,

Thanks again. I know it will work but I try tio avoid it since it will have a knock on effect to other files.

regards,

Ivan