number stored as text in Excel based report

[mstoler]

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

[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]

How are you exporting to Excel?

[mstoler]

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

[mstoler]

I am using NPrinting to create an excel based report.

[Frank_S]

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.

[Daniel_Jenkins · Accepted solution]

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]

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]

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.