topic Re: Numeric Data stored as Text in Excel Generated using NPrinting in Qlik NPrinting

Numeric Data stored as Text in Excel Generated using NPrinting

tanmay_bhardwaj — Mon, 18 Mar 2024 12:10:03 GMT

I have couple of reports which I am sending as excel attachment via NPrinting email. In them I have columns which have numerical data e.g. Amounts.

The problem is in the excel attachment these columns get converted into 'text' type due to which sub-totals are not calculated.

I have already taken below steps to troubleshoot this:

1. The specific column is 'measure' in the Sheet (straight table widget) with 'Number' formatting.

2. In the NPrinting template of the report have disable 'Keep Source Formats'.

3. Have changed the format of the column to 'Number' in the NPrinting excel template.

Please advise what can I do to solve this.

Re: Numeric Data stored as Text in Excel Generated using NPrinting

Lech_Miszkiewicz — Mon, 18 Mar 2024 13:02:33 GMT

Have you regenerated metadata after doing all the steps?

Re: Numeric Data stored as Text in Excel Generated using NPrinting

Lech_Miszkiewicz — Mon, 18 Mar 2024 22:32:56 GMT

Few more things to consider:

what is the qlik sense version? Is it compatible as per documentation ?
what is the object? It does not look like table object and it looks more like the new extension straight table. If that is the case for testing i would suggest building that object from scratch using standard table object
what are your regional settings of Qlik Sense app, nprinting server and excel especially around formats for decimal and thousand delimiters.

Re: Numeric Data stored as Text in Excel Generated using NPrinting

tanmay_bhardwaj — Mon, 18 Mar 2024 13:45:48 GMT

yes, I did.

Re: Numeric Data stored as Text in Excel Generated using NPrinting

Lech_Miszkiewicz — Mon, 18 Mar 2024 22:55:43 GMT

Hi, did you read my other comment regarding using the objects? Your straight table looks like the one from extensions bundles which is listed as not supported: https://community.qlik.com/t5/Qlik-NPrinting/Numeric-Data-stored-as-Text-in-Excel-Generated-using-NPrinting/m-p/2431914#M41266

Re: Numeric Data stored as Text in Excel Generated using NPrinting

tanmay_bhardwaj — Thu, 21 Mar 2024 11:55:13 GMT

@Lech_Miszkiewicz , I updated the subtotal formula as indicated in the the screenshot (reduced the number of rows to consider for sub-total) and started getting the results. Thanks for your help all the way.

Re: Numeric Data stored as Text in Excel Generated using NPrinting

Lech_Miszkiewicz — Fri, 22 Mar 2024 04:26:03 GMT

Hi.

I am glad it is working for you but I think there is still issue with your template. If you were building your template according to best practices you would have subtotals with for cells L3:L4 and on row 4 you would have <deleterow> tag and that would thake care of everything given that you have also "Expand Range" enabled.

The subject of the question was not subtotoal though but the fact that cells were exported as text - what solved that in your case?

cheers

Re: Numeric Data stored as Text in Excel Generated using NPrinting

tanmay_bhardwaj — Fri, 22 Mar 2024 13:26:53 GMT

Hi @Lech_Miszkiewicz , I forgot to add, another change I did after your suggestion was to use standard table widget/object instead of straight table widget/object. I believe this change helped solve the problem.

Below is the summary of the settings/changes I did which were eventually successful and helped me get the numeric data as number instead of text.

1. Used standard table widget/object instead of straight table widget/object.

2. In the table object set the particular measure column as 'Number' in 'Number Formatting'.

3. In NPrinting removed the 'Keep Source Format' setting.

4. Changed the format of the excel cell containing the particular measure to 'Number'.

5. Hide blank rows and columns as per the suggestion given in this article (https://community.qlik.com/t5/Qlik-NPrinting/NPrint-Excel-Hide-Trailing-Empty-Rows/td-p/1275370)

For sub-total on top of the column I changed the formula to have L3:L4 (changed again after your recent reply).

After following all these steps the numbers are not stored as text anymore and the sub-totals on them are also working fine.

For anyone reading this question in future please use the below link for NPrinting user guide.

https://help.qlik.com/en-US/nprinting/February2024/Content/NPrinting/Introduction/Guides.htm

Re: Numeric Data stored as Text in Excel Generated using NPrinting

Lech_Miszkiewicz — Fri, 22 Mar 2024 21:51:30 GMT

Hi @tanmay_bhardwaj - appreciate your summary!. Thank you!