topic String date to format date in App Development

String date to format date

Kentucky — Mon, 31 May 2021 14:08:38 GMT

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

Re: String date to format date

rwunderlich — Mon, 31 May 2021 15:06:35 GMT

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

-Rob

Re: String date to format date

Kentucky — Mon, 31 May 2021 15:11:26 GMT

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

Re: String date to format date

MarcoWedel — Mon, 31 May 2021 19:28:18 GMT

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