Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I would like to use an Excelsheet for analysis, but I have some issues with the interpretation of a date field. In Excel the date records look something like this:
9/30/2014 5:06:55 PM |
9/30/2014 5:12:19 PM |
9/30/2014 5:12:39 PM |
9/30/2014 5:15:59 PM |
9/30/2014 5:58:36 PM |
10-1-2014 |
10-1-2014 |
10-2-2014 |
10-2-2014 |
10-3-2014 |
All date-records should have the MM/DD/YYYY format, but Excel shows the first records as text. The last records are interpreted as date, but with a wrong format(DD-MM-YYYY instead of MM-DD-YYYY).
Is there an easy way to read all these records in the right format without having to change the Excel-data?
LOAD
DATE(FLOOR(ALT(
Date#(F1,'M/D/YYYY h:mm:ss TT'), // Try first format
Date#(F1,'M-D-YYYY') // then second
))) as Date
INLINE [
F1
9/30/2014 5:06:55 PM
9/30/2014 5:12:19 PM
9/30/2014 5:12:39 PM
9/30/2014 5:15:59 PM
9/30/2014 5:58:36 PM
10-1-2014
10-1-2014
10-2-2014
10-2-2014
10-3-2014
];
Hi Perry,
It is not good that your Excel spreadsheet has duff values in. If you can fix the source it will be better all round.
You should be able to deal with what you have here though, with a bit of code something like:
LOAD
Date(if(Alt(DateField, 0) * 1 <> 0,
DateField,
Date#(DateField, 'D/MM/YYYY h:mm:ss TT')),
'MM/DD/YYYY') as CorrectDate,
The code with the Alt and the multiplication is a sure fire way to check you have a valid date field in Excel (don't worry about how it looks in Excel - this is not important), if it is a valid date you can just take it as is. The Date# code is converting from string to a proper date, the parameter is the format of the string we are reading. The IF block is wrapped in a Date statement which then formats the field how we want it.
Hope that helps,
Steve
please post an Excel sample.
thanks
regards
Marco
thanks for the fast responses.
I know it's not very wise to use different formats, but the data comes from a machine running with a MM-DD-YYYY - format as default and my system uses a DD-MM-YYYY as default format. I have +100k of records, so I'm not eager to change the Excel-sheets.
The script of Clever works partly (the first 12 days of the month aren't recognized als shown in the pic).
It seems I'm not able to post an Excel sample. How can I do that?
regards
Perry
regards
Marco
I think I just solved it. Maybe not the most elegant way of doing this, but it seems to work.
Based on the input of Clever I made the following script:
Date(Floor(alt(
date#(F1,'M/D/YYYY h:mm:ss TT'),
MakeDate(YEAR(F1),DAY(F1),month(F1))))) as Date
By using the makedate function I was able to swap the month and day. Not sure if it generates any other problems along the way, but for now it works.
Thanks for your help.
In this post the data I have to deal with.
Hi,
another solution could be:
Timestamp(Alt(TimeString,Timestamp#(TimeString,'MM/DD/YYYY hh:mm:ss TT'))) as Timestamp
and a preceding load
DayName(Timestamp) as Date
to get both the Timestamp as well as the Date information:
table1:
LOAD *,
DayName(Timestamp) as Date;
LOAD StateAfter,
TimeString,
Timestamp(Alt(TimeString,Timestamp#(TimeString,'MM/DD/YYYY hh:mm:ss TT'))) as Timestamp,
MsgText
FROM [https://community.qlik.com/servlet/JiveServlet/download/783209-166622/demodata.xlsx] (ooxml, embedded labels, table is [Copy ]);
hope this helps
regards
Marco
Hi Perry
I have had issues with dates in excel files when converting from mm/dd/yyyy to the correct way of dd/mm/yyyy. Excel likes to take a guess at what you mean and makes changes as it sees fit, so if it sees a date with the day of 13 or more it knows that is the day and switches but if the day is 12 or less it assumes it's the month and keeps it. No messages or warnings just "fixes" it.
To fix the dates in excel prior to loading into Qlikview would be my tactic. I use a macro that sets the formula of a cell to the cell value. This by default gets excel to treat the dates as mm/dd/yyyy. I tried it with your sample data and all of your dates were treated as October. It doesn't seem right to post excel macros on a QlikView post. So if such things offend, please look away
Ron
VBA Code block--
Sub TrimInPlace()
' Ron Campbell
'Highlight a section of cells
'step through cell by cell trimming the contents
'Trimming will only occur on visible cells, the macro skips any rows that are hidden or not visible as a result of a filter
If ActiveWorkbook Is Nothing Then
Exit Sub
End If
On Error GoTo ErrorHandler
Application.Calculation = xlCalculationManual
Dim currCell As Range
If Selection.Count > 1 Then
Selection.SpecialCells(xlCellTypeVisible).Select
End If
For Each currCell In Selection.Cells
If Not Application.IsNA(currCell) Then
currCell.Formula = Replace(Replace(Replace(Trim(currCell.Value), Chr(160), ""), Chr(10), ""), " ", " ")
End If
Next
'restore the user's calculation setting
Application.Calculation = xlCalculationAuto
Exit Sub
ErrorExit:
'restore the user's calculation setting
Application.Calculation = xlCalculationAuto
'the ErrorHandler code should only be executed if there is an error
Exit Sub
ErrorHandler:
Debug.Print Err.Number & vbLf & Err.Description
Resume ErrorExit
End Sub