Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser222
Creator
Creator

Load numbers from excel cell

Hi Everyone,

We have excel file with a column which has values like 5.91702E+13 and Qlik is loading the value as it is and its difficult to do further analysis.

How can we covert the values like  5.91702E+13 into its actual number which is  like 59171111111179 while loading into Qlik?

Thanks

Labels (2)
4 Replies
TimvB
Creator II
Creator II

If the values in Excel are text, the following will do the trick:

=subfield(upper(FieldSci), 'E',1) *pow(10,subfield(upper(FieldSci), 'E',2))

Otherwise, try:

=Num#(FieldSci)

petter
MVP
MVP

No - Qlik perfectly well knows how to interpret scientific notation ... no need to go to complications doing text manipulation.

 

 

 

 

petter
MVP
MVP

To interpret numbers properly one can use Num#()

 

Num#(aNumber) will often keep the formatting and create a Dual-value.

 

Doing a calculation which is not changing the original number like multiplying with 1 or adding a 0 is a way of tricking Qlik into not keeping the original format.

 

Num#('1E3')+0

will give 1000

whereas Num#('1E3') will give a dual-number of 1000 with the format 1E3.

 

 

Brett_Bleess
Support (Former)
Support (Former)

If any of the posts you have received with explanations and ideas to address have worked, do not forget to return to the thread and on the post that helped the most, use the Accept as Solution button to mark it, which will give the poster credit for the help and let other Members know this worked for your use case.  If you are still working on things, please leave an update with what you need, it is great if we can close the thread properly if you have what you need.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.