topic When we export to excel then i'm facing date formatt issues in QlikView

When we export to excel then i'm facing date formatt issues

Srinivas — Wed, 01 Apr 2015 19:06:31 GMT

Hi All,

Please see below code, Here in Qlikview front-end level showing correct format , but when we are export into excel or csv file then it's showing problem.

Date:

LOAD
Date(OrderDate,'YYYY-MM-DD') as OrderDate,
Date(RETD,'YYYY-MM-DD') as RETD,
Date(CETD,'YYYY-MM-DD') as CETD,
UnitCostOfLine
FROM

(qvd);
STORE Date into D:\Personal\Test.Csv(txt);

Qlikview front-end report see below :

orderdate ,RETD CETD

2014-12-02,2015-02-06,2015-02-06

2014-12-02,2015-02-13,2015-02-13

2014-12-02,2015-02-20,2015-02-20

2014-12-02,2015-02-27,2015-02-27

2014-12-02,2015-02-03,2015-02-03

Csv output : this below one is wrong .... I want to see above qlikview front-end format..

OrderDate	RETD	CETD	UnitCostOfLine
02-12-2014	06-02-2015	06-02-2015
02-12-2014	27-02-2015	27-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	27-02-2015	27-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	03-03-2015	03-03-2015
02-12-2014	20-02-2015	20-02-2015
02-12-2014	13-02-2015	13-02-2015
02-12-2014	20-02-2015	20-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	27-02-2015	27-02-2015
02-12-2014	06-02-2015	06-02-2015
02-12-2014	03-03-2015	03-03-2015
02-12-2014	20-02-2015	20-02-2015
02-12-2014	06-02-2015	06-02-2015

Could you please help on this.

Thanks & Regards,

Munna

Re: When we export to excel then i'm facing date formatt issues

sunny_talwar — Wed, 01 Apr 2015 19:23:05 GMT

Your front end table doesn't have UnitCostOfLine may be you want to remove that field before you save?

Best,

Sunny

Re: When we export to excel then i'm facing date formatt issues

Srinivas — Wed, 01 Apr 2015 23:54:28 GMT

Hi Sun,

As of now UnitCostOfLine is not required but I want to see correct date format in excel level.when am apply in script level code that's worked in qlikview front level.

But after export to excel or csv file through store command then it's not coming correct format.

Advance Thanks,

Munna

Re: When we export to excel then i'm facing date formatt issues

robert_mika — Thu, 02 Apr 2015 00:30:50 GMT

The only option I see is to save as Text.

After Store,Open the file and fallow the wizard as you get to this stage

Result

Re: When we export to excel then i'm facing date formatt issues

Alejandro_Hernández — Thu, 02 Apr 2015 00:58:20 GMT

Are you seeing this date format in excel or opening the .csv file in notepad?

This is how the generated .csv looks in notepad:

OrderDate,RETD,CETD,UnitCostOfLine

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-02-27,2015-02-27,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-02-27,2015-02-27,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-03-03,2015-03-03,

2014-12-02,2015-02-20,2015-02-20,

2014-12-02,2015-02-13,2015-02-13,

2014-12-02,2015-02-20,2015-02-20,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-02-27,2015-02-27,

2014-12-02,2015-02-06,2015-02-06,

2014-12-02,2015-03-03,2015-03-03,

2014-12-02,2015-02-20,2015-02-20,

2014-12-02,2015-02-06,2015-02-06,

This is how it looks in Excel:

OrderDate	RETD	CETD
02-12-14	06-02-15	06-02-15
02-12-14	27-02-15	27-02-15
02-12-14	06-02-15	06-02-15
02-12-14	27-02-15	27-02-15
02-12-14	06-02-15	06-02-15
02-12-14	03-03-15	03-03-15
02-12-14	20-02-15	20-02-15
02-12-14	13-02-15	13-02-15
02-12-14	20-02-15	20-02-15
02-12-14	06-02-15	06-02-15
02-12-14	27-02-15	27-02-15
02-12-14	06-02-15	06-02-15
02-12-14	03-03-15	03-03-15

Excel uses the date format you defined in Control Panel-Regional Settings.

Re: When we export to excel then i'm facing date formatt issues

Srinivas — Thu, 02 Apr 2015 01:09:07 GMT

HI Robert,

can we change in Qlikview edit script level that would be better, this is only sample file like this I have to export every day lot of files so that can you give any idea.

Advance Thanks,

Munna