Reading in different date formats

[#1]

Hi,

I have a script that reads in 3 excel files, all with the same fields but data from 3 different regions. One of the fields is a create_date field which is of timestamp format in the following format:

1st excel file: 'DD/MM/YYYY hh/mm/ss TT'  -> example: '02/06/2016 11:30:00 AM'

2nd excel file: 'DD/MM/YYYY hh/mm/ss TT'  -> example: '02/06/2016 11:30:00 AM'

3rd excel file: 'MM/DD/YYYY hh/mm/ss TT'  -> example: '06/02/2016 11:30:00 AM'

As you can see, the 3rd excel file has a different time format, so when I read it into qlikview, it is giving me 6 Feb 2016 which is wrong (it is supposed to be 2 Jun 2016).

My script is just a simple load statement:

Raw_Transformed:

LOAD

COUNTRY,

create_date

FROM

[Excel1.xlsx]

(ooxml, no labels, header is 1 lines, table is [Excel1]);

concatenate

LOAD

COUNTRY,

create_date

FROM

[Excel2.xlsx]

(ooxml, no labels, header is 1 lines, table is [Excel2]);

concatenate

LOAD

COUNTRY,

create_date

FROM

[Excel3.xlsx]

(ooxml, no labels, header is 1 lines, table is [Excel3]);

store Raw_Transformed into [Raw_Transformed.qvd];

At the end of this load statement, the create date is returned as the following values for example: 42004.758333333

I then format this value when I load the data into the dashboard using the following formula: date(floor(create_date),'DD/MM/YYYY')

The problem is because my format in the 3rd excel file is different, I'm getting the wrong date values for it. Please help on how I can resolve this, thanks.

[Gysbert_Wassenaar]

I think your problem is that the dates aren't correctly entered in one or more excel files. If the dates were entered as real dates they would have numeric values and Qlikview would get the correct values no matter what format they had in excel. But if the values in Excel are text values then Qlikview will have to interpret the text values and make dates from them. And in that case Qlikview cannot distinguish between June 2nd and February 6th from 02/06/2016 unless you explicitly specify which date format should be used. So you need to make sure all the excel files contain numeric values (formatted as dates) or use the Date# or TimeStamp# function and explicitly specify the date format for the specific excel file.

[sorrakis01]

Hi,

Try this:

Date(Date#(create_date,'MM/DD/YYYY'), 'DD/MM/YYYY') AS New_Date

Regards

[maxgro]

// if this is your excel data

excel:

load * inline [

excel, dt

1, 02/06/2016 11:30:00 AM

2,  02/06/2016 11:35:00 PM

3,  06/02/2016 11:30:00 AM

];

//

// you can format the excel as date or timestamp

// the format is different for excel 1,2 and excel 3

//

final:

load

  excel, dt,

  if(excel='1' or excel='2', Date(Floor(Date#(dt, 'DD/MM/YYYY hh:mm:ss TT'))),

  if(excel='3', Date(Floor(Date#(dt, 'MM/DD/YYYY hh:mm:ss TT')))

  )) as date,    // date only

  if(excel='1' or excel='2', Timestamp(Date#(dt, 'DD/MM/YYYY hh:mm:ss TT')),

  if(excel='3', TimeStamp(Date#(dt, 'MM/DD/YYYY hh:mm:ss TT'))

  )) as datetime    // date and time

Resident excel;

DROP Table excel;

[sunny_talwar]

For you current script, you can try this:

SET TimestampFormat='DD/MM/YYYY hh/mm/ss TT';

Raw_Transformed:

LOAD

COUNTRY,

create_date

FROM

[Excel1.xlsx]

(ooxml, no labels, header is 1 lines, table is [Excel1]);

concatenate

LOAD

COUNTRY,

create_date

FROM

[Excel2.xlsx]

(ooxml, no labels, header is 1 lines, table is [Excel2]);

concatenate

LOAD

COUNTRY,

TimeStamp(TimeStamp#(create_date, 'MM/DD/YYYY hh/mm/ss TT')) as create_date

FROM

[Excel3.xlsx]

(ooxml, no labels, header is 1 lines, table is [Excel3]);

Store Raw_Transformed into [Raw_Transformed.qvd];

[MarcoWedel]

please post samples of your excel sources to work with

thanks

regards

Marco

[jagan]

Hi,

Try like this using Alt() to handle multiple date formats

Data:

LOAD

COUNTRY,

TimeStamp(Alt(TimeStamp#(create_date, 'DD/MM/YYYY hh/mm/ss TT'), TimeStamp#(create_date, 'MM/DD/YYYY hh/mm/ss TT')))AS TimeStamp

FROM

[Excel1.xlsx];