Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Could you attached your excel file?
try
Num#(trim(HeadCount)) as HeadCount
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.
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
Still not working
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
Still no hope
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.
Num#( KeepChar(trim(HeadCount),'0123456789')) as HeadCount