topic Re: Issue with importing numbers with Excel accounting format in App Development

Issue with importing numbers with Excel accounting format

wayne-a — Tue, 10 Sep 2019 13:53:18 GMT

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

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

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

Re: Issue with importing numbers with Excel accounting format

Vegar — Tue, 10 Sep 2019 14:00:59 GMT

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

Re: Issue with importing numbers with Excel accounting format

DavidM — Tue, 10 Sep 2019 14:02:32 GMT

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"

Re: Issue with importing numbers with Excel accounting format

wayne-a — Tue, 10 Sep 2019 17:32:22 GMT

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.

Re: Issue with importing numbers with Excel accounting format

wayne-a — Tue, 10 Sep 2019 18:01:18 GMT

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.