Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Kentucky
Contributor II
Contributor II

String date to format date

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);

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

Kentucky
Contributor II
Contributor II
Author

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