topic Re: Please help how to format date in QlikView

Please help how to format date

suvechha_b — Mon, 13 Jul 2015 09:56:20 GMT

Facing problem when loading date field from excel file.

Please find the excel file and sample model attached.

The problem is on date format of Start Date as because my source excel file having start date : 2015/02/23

I have formatted to "dd/mm/yyyy" in excel. But it is showing in excel but pulling wrong data in qlikview.

Re: Please help how to format date

vardhancse — Mon, 13 Jul 2015 09:57:31 GMT

Once check the default date format in script editor

SET DateFormat='MM/DD/YYYY';

Re: Please help how to format date

MK_QSL — Mon, 13 Jul 2015 09:58:45 GMT

Use

=Date([Start Date])

as calculated dimension

Re: Please help how to format date

Mon, 13 Jul 2015 10:03:04 GMT

Hi,

Try like below in caculated dimension,

=date([Start Date],'DD-MM-YYYY')

Then change label

Re: Please help how to format date

vardhancse — Mon, 13 Jul 2015 10:08:39 GMT

Hi,

In script level we can use like

Date(Floor([Start date])) as Start_Date,

Time(Floor([Start date])) as Start_Time

Re: Please help how to format date

suvechha_b — Mon, 13 Jul 2015 10:08:45 GMT

Its not working.

Re: Please help how to format date

suvechha_b — Mon, 13 Jul 2015 10:09:02 GMT

I tried it but its not working

Re: Please help how to format date

suvechha_b — Mon, 13 Jul 2015 10:09:38 GMT

tried but not working

Re: Please help how to format date

vardhancse — Mon, 13 Jul 2015 10:11:07 GMT

Might be issue with the excel data format once try to change in excel sheet and try to relaod

Re: Please help how to format date

sibideepak — Mon, 13 Jul 2015 10:11:30 GMT

Hi suvechha.b,

Sometimes some extra spaces in the excel cause this problem,

so when you're loading script try,

date(trim([Start Date]),'DD-MM-YYYY')

Re: Please help how to format date

suvechha_b — Mon, 13 Jul 2015 10:14:10 GMT

tried but not working

Re: Please help how to format date

sibideepak — Mon, 13 Jul 2015 10:15:38 GMT

Hi,

Sometimes some extra spaces in the excel cause this problem,

so when you're loading script try,

date(trim([Start Date]),'DD-MM-YYYY')

Re: Please help how to format date

Mon, 13 Jul 2015 10:16:07 GMT

You can try at script level like below & use below field in table,

date([Start Date],'dd-mm-yyyy') as New_Start_Date

& let me know what is the output?

Re: Please help how to format date

suvechha_b — Mon, 13 Jul 2015 10:19:22 GMT

See the excel file image

In excel I formated it in DD/MM/YYY format . it is showing on excel file but when I select it the value is showing "2015/02/23 "

Re: Please help how to format date

suvechha_b — Mon, 13 Jul 2015 10:24:42 GMT

When I am using trim I am getting the value correct: "23/02/2015"

But all the other dates are not pulling , they are blank

Re: Please help how to format date

vardhancse — Mon, 13 Jul 2015 10:26:35 GMT

Once check the default date format in the script editor.

SET DateFormat='DD/MM/YYYY';

Or else in the chart/table where you are seeing the Start date field once check the number format in properties->number<tab>

select Start date->Date

Re: Please help how to format date

settu_periasamy — Mon, 13 Jul 2015 10:26:40 GMT

Hi,

Can you try the below attached excel? i formatted the Start Date and End End using the Text to columns.

Just try this.

Re: Please help how to format date

senpradip007 — Mon, 13 Jul 2015 10:38:34 GMT

Have a look on this.

Re: Please help how to format date

Mon, 13 Jul 2015 10:45:53 GMT

do following changes in excel it works,

1) change all start Date custom format to dd/mm/yyyy by selecting each cell value.

In your script do single change like below,

SET DateFormat='DD/MM/YYYY';

& Take [Start Date] field to check resulted field.

Re: Please help how to format date

suvechha_b — Mon, 13 Jul 2015 11:13:52 GMT

Can any one help me how to write in expression level.

As I dont want to change the script of qlikview.

Is going to affect other calculation.