Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Partner
Partner

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
Highlighted

Re: Numbers not detecting from excel file.

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
Highlighted

Re: Numbers not detecting from excel file.

Could you attached your excel file?

Highlighted
Honored Contributor III

Re: Numbers not detecting from excel file.

try

Num#(trim(HeadCount)) as HeadCount

Highlighted
Not applicable

Re: Numbers not detecting from excel file.

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.

Highlighted

Re: Numbers not detecting from excel file.

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

Highlighted
Partner
Partner

Re: Numbers not detecting from excel file.

Still not working

Highlighted

Re: Numbers not detecting from excel file.

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

Highlighted
Partner
Partner

Re: Numbers not detecting from excel file.

Still no hope

Highlighted
MVP
MVP

Re: Numbers not detecting from excel file.

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.

Highlighted
Honored Contributor III

Re: Numbers not detecting from excel file.

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