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: 
wayne-a
Creator
Creator

Issue with importing numbers with Excel accounting format

Hi, I'm having a problem importing an excel file where the numbers are formatted as "Accounting" format.  This puts parenthesis around negative numbers.  Upon importing the value it seems to recognize it as a negative and shows it correctly when displayed in a table but doesn't total it, and when multiple spreadsheets are loaded is treating it like character data.  I've tried to put a Num function in the load script with various formats but this hasn't helped.  Thanks in advance for any help.

Cell formatting in Excel

clipboard_image_0.png

 

Displayed in Qlik Sense Table,  value displays correctly but totals don't calculate

clipboard_image_1.png

 

Load script function (tried with various formats, none worked)

clipboard_image_2.png

 

1 Solution

Accepted Solutions
wayne-a
Creator
Creator
Author

Based on the behavior with the total I decided to change the measure from just the field value to Sum(field) instead.  That worked, and the total started working in auto mode as well.  Very strange but at least found a solution.

View solution in original post

4 Replies
Vegar
MVP
MVP

Try to replace num(...) with num#(...)

DavidM
Partner - Creator II
Partner - Creator II

How about this:

If( Left("Period 4 - 2020",1)=chr(40),

PurgeChar(PurgeChar("Period 4 - 2020",chr(40)),chr(41))*(-1),  "Period 4 - 2020") as "Period 4 - 2020"

wayne-a
Creator
Creator
Author

Thanks Vegar and David M.  Unfortunately neither of those worked, even tried combining the ideas by putting a Num# in front of the PurgeChar.  It seems that Qlik recognizes it as a number based on what I see in the data manager but for some reason isn't rendering or summing correctly.  I was able to get it to sum when I chose "Sum" for how to handle the total of that field but when importing multiple worksheets it doesn't handle it correctly.  Thanks again.  

wayne-a
Creator
Creator
Author

Based on the behavior with the total I decided to change the measure from just the field value to Sum(field) instead.  That worked, and the total started working in auto mode as well.  Very strange but at least found a solution.