String date to format date

[Kentucky]

Hi, I'm new to Qlik Sense. I 'm doing a data extraction from an .xlsx in which one of the fields that I have to add is the date. The date is included in the file name but I need to convert it to a date format since it is as a string right now. I attach an example and the upload I have made.

File:

Players_12.01.2018.xlsx

My load:

LOAD
PLAYER,
POSITION as PositionPlayer,
TEAM as TeamPlayer,
Cr as CrPlayer,
TOT as TotalsPlayer,
SubField (SubField (FileName (), '_', -1), '. Xlsx', 1) as PlayerDate
FROM [lib: //......./Players_*.xlsx]
(ooxml, embedded labels, table is Sheet1);

[rwunderlich]

Date(Date#(
SubField (SubField (FileName (), '_', -1), '. Xlsx', 1) 
,'MM.DD.YYYY')) as PlayerDate

-Rob

[Kentucky]

Thanks a lot Rob. The format of the load is now correct 🙂

[MarcoWedel]

Hi,

you could also purge the file extension using the FileBaseName() function instead:

Date#(SubField(FileBaseName(),'_',-1),'DD.MM.YYYY') as PlayerDate

hope this helps

regards

Marco

Edit: having issues with the editor, hope the reply is accepted this time