Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi,
Try this:
Date(Date#(create_date,'MM/DD/YYYY'), 'DD/MM/YYYY') AS New_Date
Regards
// 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;
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];
please post samples of your excel sources to work with
thanks
regards
Marco
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];