NPriniting On-Demand report Date format

[QlikGeek]

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  Qlik NPrinting QlikView 

[AtQlik]

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. 

[Lech_Miszkiewicz · Accepted solution]

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.

[QlikGeek]

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.  

[Ruggero_Piccoli]

Hi,

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

Best Regards,

Ruggero

[Frank_S]

@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#...

[QlikGeek]

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:  

[QlikGeek]

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?  

[QlikGeek]

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,