Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have 2 date feilds in a table in excel file, but some of the dates are in string format and some are in number format in my excel file.
See below table,
EmpId | StartDate | EndDate |
1 | 30/09/2014 | 20/01/2015 |
1 | 21/01/2015 | 2/3/2015 |
1 | 3/3/2014 | 31/01/2016 |
2 | 10/3/2015 | 21/06/2015 |
2 | 22/06/2015 | 20/09/2015 |
6 | 10/11/2015 | 31/01/2016 |
7 | 2/10/2015 | 31/01/2016 |
8 | 17/11/2014 | 22/02/2015 |
8 | 23/02/2015 | 4/3/2015 |
8 | 5/3/2015 | 31/01/2016 |
9 | 18/11/2014 | 2/3/2015 |
9 | 3/3/2015 | 31/01/2016 |
Now I want to make all dates into a single format.i.e Number format in qlikview. I have tried to used DATE() and DATE#() functions, but I think those are not giving desired results.
any ideas please?..
Thank you.
How would you identify the format of these dates:
can you post your sample excel file.
or
use makedate function it may help.
The Alt function is a great way of trying out multiple parse strings and returns the first one that successfully parses. Use something like:
Date(Alt(StartDate, Date#(StartDate, 'dd/MM/yyyy'), Date#(StartDate,'d/M/yyyy))) as StartDate,
This will parse a numeric value, but if it is a string, will try dd/MM/yyyy and then d/M/yyyy to get the date,.
As jonathan said, if you know the Date formats of the files then using alt() is a convenient one
First read this very good post on Date and Date#: The Date Function
Either load script will work depending on your default date/timestamp format settings. If you have a mix of M/D/YYYY and D/M/YYYY date formats, you'll need some kind of indicator field to determine whether to use one format or the other or you'll need to make a logic decision as you load data to make one format the primary format.
If your default format is M/D/YYYY and your data is in D/M/YYYY format, you need to use Date#.
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
LOAD
*,
Date#(StartDate, 'DD/MM/YYYY') as [Date#StartDate],
Date#(EndDate, 'DD/MM/YYYY') as [End#StartDate],
Date(Date#(StartDate, 'DD/MM/YYYY'), 'MMM-DD-YYYY') as [MMM-DD-YYYYStartDate],
Date(Date#(EndDate, 'DD/MM/YYYY'), 'MMM-DD-YYYY') as [MMM-DD-YYYYEndDate]
;
LOAD * INLINE [
EmpId, StartDate, EndDate
1, 30/09/2014, 20/01/2015
1, 21/01/2015, 2/3/2015
1, 3/3/2014, 31/01/2016
2, 10/3/2015, 21/06/2015
2, 22/06/2015, 20/09/2015
6, 10/11/2015, 31/01/2016
7, 2/10/2015, 31/01/2016
8, 17/11/2014, 22/02/2015
8, 23/02/2015, 4/3/2015
8, 5/3/2015, 31/01/2016
9, 18/11/2014, 2/3/2015
9, 3/3/2015, 31/01/2016
]
;
If your default format is D/M/YYYY and your data is in D/M/YYYY format there is no need to use Date#
SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
LOAD
*,
Date(StartDate, 'MMM-DD-YYYY') as [MMM-DD-YYYYStartDate],
Date(EndDate, 'MMM-DD-YYYY') as [MMM-DD-YYYYEndDate]
;
LOAD * INLINE [
EmpId, StartDate, EndDate
1, 30/09/2014, 20/01/2015
1, 21/01/2015, 2/3/2015
1, 3/3/2014, 31/01/2016
2, 10/3/2015, 21/06/2015
2, 22/06/2015, 20/09/2015
6, 10/11/2015, 31/01/2016
7, 2/10/2015, 31/01/2016
8, 17/11/2014, 22/02/2015
8, 23/02/2015, 4/3/2015
8, 5/3/2015, 31/01/2016
9, 18/11/2014, 2/3/2015
9, 3/3/2015, 31/01/2016
]
;
Hi Sunny,
Please refer attached excel file for date format.
when I use Date#() function result is like below image.
here some dates are in number format so Date#() function interprest the dates which are in string format.
if I use Date() function result is like this(there no third date associated with EmpId 1)
I want all dates to be shown in same format.