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: 
amithmurali
Partner - Creator II
Partner - Creator II

Numbers not detecting from excel file.

Hi guys,

I have a problem with an excel file.

i have headcount from excel file. i created qvds for it. but when i try to get the sum of the headcount. its not giving me the exact count.

there is some kind of formatting in the excelfile which i cant figure out. please find the app attached.

1 Solution

Accepted Solutions
Kushal_Chawda

Your Excel file Field format is not proper. Some values are text and some are numbers. Create the field in script using below conditions it will solve your problem

if(IsNum(HeadCount),HeadCount, KeepChar(trim(HeadCount),'0123456789')) as HeadCount

See the attached

View solution in original post

11 Replies
robert_mika
Master III
Master III

Could you attached your excel file?

sasiparupudi1
Master III
Master III

try

Num#(trim(HeadCount)) as HeadCount

Not applicable

Try saving the excel file into a csv file and open it with a text editor. Maybe there you will see the format of the numbers and if it's giving you trouble.

You could also try to load that csv file into QlikView and see if the results are the same.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

When reading from the intermediate QVD, instead of

Num#(HeadCount) AS HeadCount, ...

try with

Num#(trim(HeadCount)) AS HeadCount, ...

Leading and/or trailing spaces are often close to invisible in listboxes. But the fact that some HeadCount values are left-aligned (meaning that they are strings) should give you a clue.

Best,

Peter

amithmurali
Partner - Creator II
Partner - Creator II
Author

Still not working

Kushal_Chawda

Your Excel file Field format is not proper. Some values are text and some are numbers. Create the field in script using below conditions it will solve your problem

if(IsNum(HeadCount),HeadCount, KeepChar(trim(HeadCount),'0123456789')) as HeadCount

See the attached

amithmurali
Partner - Creator II
Partner - Creator II
Author

Still no hope

swuehl
MVP
MVP

You can remove all kinds of whitespace when loading the data in (when loading from Excel, values should already be trim()'med by default, I believe):

Num#( Purgechar( HeadCount, Chr(09) & Chr(32) & Chr(160) & Chr(12288)) ) as HeadCount

If this resolves your issue, it's best to do this when preparing the QVD.

sasiparupudi1
Master III
Master III

Num#( KeepChar(trim(HeadCount),'0123456789')) as HeadCount