topic NPriniting On-Demand report Date format in Qlik NPrinting

NPriniting On-Demand report Date format

QlikGeek — Thu, 06 May 2021 01:51:59 GMT

Hello Qlik Geeks,

I have created an on-demand Excel report using (Qlik NPrinting April 2020 | Version: 20.10.2.0) and I am using QlikView April 2020 | Version 12. 50. I have a straight table in QVW with columns Date and Account number But the generated report is showing the two column values completely incorrect. Account numbers has '0' missing before them and Date values are some random numbers. Can you please help me with this?

QVW:

Excel:

@sunny_talwar

Re: NPriniting On-Demand report Date format

AtQlik — Thu, 06 May 2021 02:14:46 GMT

Check for the data format set in the excel cells. Sometimes if the cell in excel template is set as general then you might see some difference in value.

Re: NPriniting On-Demand report Date format

Lech_Miszkiewicz — Thu, 06 May 2021 04:01:18 GMT

so the date is not random value but it is numerical representation of that date. put 44287 into excel and change its format to date and you will see that it is 01/04/2021

in regards to "leading zeros" - excel may truncate them, because it things that your account is a number. For that to work you may need to force it to be text in QlikView by applying Text() function in load script on that field.

Re: NPriniting On-Demand report Date format

QlikGeek — Thu, 06 May 2021 14:05:03 GMT

Thanks for your reply. How come it is working fine on QVW but not on NP generated on-demand excel ? It was working fine earlier before NP was upgraded to new version.

Re: NPriniting On-Demand report Date format

Ruggero_Piccoli — Thu, 06 May 2021 14:43:13 GMT

Hi,

QlikView 12.50 is supported starting from SR2, https://help.qlik.com/en-US/nprinting/February2021/Content/NPrinting/DeployingQVNprinting/System-requirements_engine.htm be sure you are not using an older version.

Best Regards,

Ruggero

Re: NPriniting On-Demand report Date format

Frank_S — Thu, 06 May 2021 16:06:04 GMT

@QlikGeek

Assuming your source data 'might' be 'text data and in case you need a more consistent behavior without upgrading QV, you may want to try converting your text source 'date' data from text to 'date' format using:

Date#(Date([Date],'MMMM DD,YYYY'),'YYYY.MM.DD')as DayMonth

I've created a sample QVW and attached it including source xlsx file.

You can see the space in the front of each text date field in the source file. Without the code about, the report output appears as numeric text (whether or not On Demand is used).

When using the above code, you will get the following On Demand output:

I have also attached the source data file and the test QVW in zipped format.

The code credit goes to @rob wunderlich in the permalink below:

https://community.qlik.com/t5/QlikView-App-Dev/Convert-text-to-date-format/m-p/98372/highlight/true#M751476

Re: NPriniting On-Demand report Date format

QlikGeek — Thu, 06 May 2021 17:32:23 GMT

Hi ,

I have made the script changes ad ran the on-demand but the output is same there is no difference.

Modified script : Date#(Date([OrdDate],'YYYY-MM-DD'),'YYYY-MM-DD')as OrdDate,

Output:

Re: NPriniting On-Demand report Date format

QlikGeek — Thu, 06 May 2021 17:36:35 GMT

After applying the Text() function to the field 'Account', it is working fine but the Date() function to Order Date field is not working. It is still showing the numerical representation in the generated NP on-demand excel report. Is there anything I am missing?

Re: NPriniting On-Demand report Date format

QlikGeek — Thu, 06 May 2021 20:16:56 GMT

It is working fine after making the below changes.. Thank you for your help

Old:

Account

OrdDate

New:

Text(Account) as Account,
Text(date(OrdDate,'YYYY-MM-DD')) as OrdDate,