topic Re: Numbers not detecting from excel file. in QlikView

Numbers not detecting from excel file.

amithmurali — Tue, 18 Aug 2015 06:26:49 GMT

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.

Re: Numbers not detecting from excel file.

robert_mika — Tue, 18 Aug 2015 06:37:45 GMT

Could you attached your excel file?

Re: Numbers not detecting from excel file.

sasiparupudi1 — Tue, 18 Aug 2015 06:42:14 GMT

try

Num#(trim(HeadCount)) as HeadCount

Re: Numbers not detecting from excel file.

Tue, 18 Aug 2015 06:44:11 GMT

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.

Re: Numbers not detecting from excel file.

Peter_Cammaert — Tue, 18 Aug 2015 06:51:45 GMT

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

Re: Numbers not detecting from excel file.

amithmurali — Tue, 18 Aug 2015 06:52:29 GMT

Still not working

Re: Numbers not detecting from excel file.

Kushal_Chawda — Tue, 18 Aug 2015 06:52:53 GMT

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

Re: Numbers not detecting from excel file.

amithmurali — Tue, 18 Aug 2015 06:54:45 GMT

Still no hope

Re: Numbers not detecting from excel file.

swuehl — Tue, 18 Aug 2015 06:55:36 GMT

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.

Re: Numbers not detecting from excel file.

sasiparupudi1 — Tue, 18 Aug 2015 06:55:53 GMT

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

Re: Numbers not detecting from excel file.

amithmurali — Tue, 18 Aug 2015 06:57:41 GMT

Thanks. it worked

Re: Numbers not detecting from excel file.

Kushal_Chawda — Tue, 18 Aug 2015 07:01:14 GMT

You can also simplify further

KeepChar(trim(HeadCount),'0123456789')