Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

mstoler
Contributor II

number stored as text in Excel based report

Hello,

I have an excel report where one of my fields is a Customer Number.  When I add the field to my Excel based report I get the green arrow in the upper left (Number stored as text).

I would very much like to not have this message because it does not look good.  Are there any suggestions for how to remove this warning for all users.  I know I can disable this in my copy of Excel but I want this for all external users.

Thank You,

Michael

1 Solution

Accepted Solutions
Employee
Employee

Re: number stored as text in Excel based report

Hi Michael,

As Frank mentioned, one way is to explicitly define field as numeric. E.g., Num(CtrlNo) as NCtrlNo

Another option is to change the Number format settings on the Number tab in Document Properties to Integer

Attached is a project that shows an example of both. Please extract to C:\Temp\MS folder or you will need to change the paths used in the NSQ.

HTH - Daniel.

8 Replies
Employee
Employee

Re: number stored as text in Excel based report

Michael -

You can override the source format for the field in the Excel report. You will have to deselect the default option of "Keep Source Formats" for the field, and then you can apply Excel or custom formatting to ensure it does not come in as text.

See the following: Can I apply Excel formatting to my Excel reports? – Customer Feedback for Vizubi

Employee
Employee

Re: number stored as text in Excel based report

How are you exporting to Excel?

mstoler
Contributor II

Re: number stored as text in Excel based report

I changed the format to ##### and I set the cell to numeric but I still get the message (green arrow).

mstoler
Contributor II

Re: number stored as text in Excel based report

I am using NPrinting to create an excel based report.

Employee
Employee

Re: number stored as text in Excel based report

The issue is that your source data loaded into Qlikview is in TEXT format.

If you use 'send to excel' inside the QVW, you will notice that the columns still indicate text fields in your excel output.

The solution is to modify your load script to update/convert the source data text fields to number fields.

Here is one of several community articles which discusses this text to num conversion process.

Re: How to convert text values which are in the database to numeric values

Once your source data is converted to numeric format using the load script in the QVW, then you should no longer show number as text fields in your 'send to excel' or your NPrinting excel report output.

This community user does not receive direct private messages
Employee
Employee

Re: number stored as text in Excel based report

Hi Michael,

As Frank mentioned, one way is to explicitly define field as numeric. E.g., Num(CtrlNo) as NCtrlNo

Another option is to change the Number format settings on the Number tab in Document Properties to Integer

Attached is a project that shows an example of both. Please extract to C:\Temp\MS folder or you will need to change the paths used in the NSQ.

HTH - Daniel.

MVP
MVP

Re: number stored as text in Excel based report

Hi,

You need to specify the format of the Expressions in Number tab or by using Num(), and in Dimensions you have to use Num(DimensionName, '###0') as calculated dimension  in the front end or you can also do the same in the back end load script like below

LOAD

Num(Column1, '###0') AS Column1,

'

'

'

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

markp201
Contributor III

Re: number stored as text in Excel based report

Still having issues with a zip code field.  I'm using a calculated dimension NUM(loczip,'####0')

Keep Source Format doesn't have an effect.  The green triangles still appear.

We're using 16.5 - hopefully this has been fixed in 17.

Community Browser