topic Re: Reading in different date formats in QlikView

Reading in different date formats

Sun, 21 Feb 2016 04:49:41 GMT

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.

Re: Reading in different date formats

Gysbert_Wassenaar — Sun, 21 Feb 2016 11:14:38 GMT

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.

Re: Reading in different date formats

sorrakis01 — Sun, 21 Feb 2016 11:19:18 GMT

Hi,

Try this:

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

Regards

Re: Reading in different date formats

maxgro — Sun, 21 Feb 2016 11:43:34 GMT

// 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;

Re: Reading in different date formats

sunny_talwar — Sun, 21 Feb 2016 14:12:17 GMT

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];

Re: Reading in different date formats

MarcoWedel — Sun, 21 Feb 2016 23:08:50 GMT

please post samples of your excel sources to work with

thanks

regards

Marco

Re: Reading in different date formats

jagan — Mon, 22 Feb 2016 03:56:22 GMT

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];