Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excel Lookup Issue from Qlikview export

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!

1 Solution

Accepted Solutions
rustyfishbones
Master II
Master II

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

View solution in original post

8 Replies
anandathome
Creator
Creator

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.

Not applicable
Author

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
Creator
Creator

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.

Not applicable
Author

I've attached the file in the previous post.

rustyfishbones
Master II
Master II

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

Not applicable
Author

Thanks so much Alan!

rustyfishbones
Master II
Master II

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

Not applicable
Author

Great Idea.

Thanks again