Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
mstoler
Partner - Specialist
Partner - Specialist

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
Daniel_Jenkins
Specialist III
Specialist III

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.

View solution in original post

8 Replies
Stephen_Jasionowski

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

Clever_Anjos
Employee
Employee

How are you exporting to Excel?

mstoler
Partner - Specialist
Partner - Specialist
Author

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

mstoler
Partner - Specialist
Partner - Specialist
Author

I am using NPrinting to create an excel based report.

Frank_S
Support
Support

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.

Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Daniel_Jenkins
Specialist III
Specialist III

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.

jagan
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III

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.