Excel Lookup Issue from Qlikview export

[#1]

I am having an issue when I export some qlikview files to excel. The excel output looks fine, however when I use the numbers in the excel in a lookup function, sometimes the number isn't formatted as a number.  It isn't a text either (multiplication by 1 does not force it to be a number).

It is as if the cell just "doesn't work"

If I use the number in a formula (ie. Number +1)  then that works.

Manually typing in the number into the excel file can fix the issue but there are no indications on where the issue occurs. (I only sumbled upon this when some cells weren't responding to my formulas against it)

Any advice would be greatly appreciated!

[anandathome]

Hi Eric,

     On QlikView, in object properties

go to Number tab --> set the proper type to the field in discussion.

--> then export.

This should work.

[#3]

Hi Anand,

The number is a key so I couldn't change it's type.

So what I did was that I created a dimension (in Qlikview) and multipled the key by 1 to force it into a number and   made sure that the field is an integer (I tried making it a number as well).

Both times it still didn't work with the lookup in excel.

Any other thoughts on what is happening here?

I've attached a sample of what I am seeing.

Many thanks for the quick reply.

Eric

[anandathome]

Does you excel column look like the one attached here.

Then,  Create an extra expression, with the dimension value ( just to make it available

     for formatting in Number tab.

If this does not work, please attach a screen of what the data is looking like in Excel.

Thanks,

Anand.

[#5]

I've attached the file in the previous post.

[rustyfishbones · Accepted solution]

Hi,

Did you try using Text to Columns for column A and F and remove spaces

there are preceding spaces in both Column A and F

It will work when you remove them

Regards

Alan

[#7]

Thanks so much Alan!

[rustyfishbones]

No Problem,

Why not use ApplyMap() function and add the lookup within Qlikview

Here is a video of how you can do that in Qlikview

http://youtu.be/jby-omVFNzI

Hope it Helps

[#9]

Great Idea.

Thanks again