Convert ascii/text to date field

[AnalyticsDev]

Hi,

Having an issue converting a column from excel to a date field using Qliksense.

If I look in the datamodel viewer for this particular field, "First Discovered", it shows the datatype as $ascii $text .

The values in this field are as follows:

Apr 1, 2022 20:45:53 EDT

Apr 4, 2021 20:58:24 EDT

Aug 7, 2021 18:12:29 EDT

Aug 29, 2021 10:12:31 EDT

When trying to use it in this format to determine some age buckets, obviously it isnt working.  

I would like the format to be something usable, 04/01/2022 or 09/29/2021.

I have tried all sorts of date functions and formatting such as: Date("First Discovered",  'MM/DD/YYYY'), Including Date# as well,...I have tried replacing the comma using Replace() CHR (44) into a hyphen or something... I have trimmed off the time and timezone values because it isn't really needed.  Nothing is working.

I've gotten a lot of suggestions from the forums here by searching, usually coming up with the usual date(datefield,'DD/MM/YYYY') or date(date#(datefield,'current format'),'DD/MM/YYYY').  Nothing is working.

Any thoughts?

[QFabian · Accepted solution]

Hi @AnalyticsDev , probably there is a function or format parameter to get it, but i made this using other functions.

I used a mapping table to get the number of the text month.

Data:
Load * INLINE [
Fecha
"Apr 1, 2022 20:45:53 EDT"
"Apr 4, 2021 20:58:24 EDT"
"Aug 7, 2021 18:12:29 EDT"
"Aug 29, 2021 10:12:31 EDT"
];

Month:
mapping
Load * INLINE [
Month, MonthX
Apr, 4
Aug, 8
];

Load
makedate(subfield(Fecha,' ', 3), applymap('Month', left(Fecha, 3)), subfield(subfield(Fecha, ',', 1), ' ', 2)) as Fecha2,
subfield(Fecha,' ', 3),
left(Fecha, 3),
subfield(subfield(Fecha, ',', 1), ' ', 2)
Resident Data;
drop table Data;

[rwunderlich]

Once you get rid of the " EDT",  you can parse into a date/time using Date#() with the pattern  'MMM D, YYYY h:m:s' .

I've used Mid(Fecha,1,index(Fecha, ' ', -1))  in the example below to trim off the EDT. 

That pattern will include the time portion. If you want just the time portion, you can either apply Floor() to the result or parse just the date string portion using the Mid(Index()) technique to strip out the time.

Lastly, if you want the display format to be MM/DD/YYYY, apply a Date() function to the resulting date. Don't apply Date() to the Timestamp result -- that will display like a date only but internally it will still have the time.

Data:
Load
  Fecha,
  Date#(Mid(Fecha,1,index(Fecha, ' ', -1)), 'MMM D, YYYY h:m:s') as Timestamp,
  Date(Floor(Date#(Mid(Fecha,1,index(Fecha, ' ', -1)), 'MMM D, YYYY h:m:s')), 'MM/DD/YYYY') as FlooredDate,
  Date#(Mid(Fecha,1,index(Fecha, ' ', 3)), 'MMM D, YYYY') as Date,
  Date(Date#(Mid(Fecha,1,index(Fecha, ' ', 3)), 'MMM D, YYYY'), 'MM/DD/YYYY') as DateReformatted
INLINE [
Fecha
"Apr 1, 2022 20:45:53 EDT"
"Apr 4, 2021 20:58:24 EDT"
"Aug 7, 2021 18:12:29 EDT"
"Aug 29, 2021 10:12:31 EDT"
]; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[AnalyticsDev]

Hi All,

@QFabian , thank you.  It works well.

@rwunderlich Thank you for the additional detail.

Marking this as solved.

[rwunderlich]

@QFabian You can get the month number with 

Month(Date#(left(Fecha, 3), 'MMM')) 

I don't think there is any need to create a mapping table. 

-Rob