trying to convert a string to a date by replacing the hyphen

[jmurrevcyc]

Hello -

i HAVE A DATA FEED THAT PROVIDES DATES IN THE FORMAT OF MM-DD-YYYY and when this imports Qlik Sense does not recognize it as a date. I have to go into the excel file  and replace the '-' hyphens with '/' slashes. I would like to accomplish this in the Qlik load script. I have tried using the following function, but it still interprets the values incorrectly.

Date#(replace([Payments Thru],'-','/'), 'MM/DD/YYYY') as [Payments Thru],

I have also tried 

Date#(replace([Payments Thru],'-','/'), 'M/D/YYYY') as [Payments Thru],

and

Date#([Payments Thru], 'M/D/YYYY') as [Payments Thru]

nothing is working. 

Thank you

[MatheusC]

Hi, @jmurrevcyc 

I couldn't understand the format of the data field in your table, but you can try the following:

see it like this:

DATE( DATE#( [Payments by],'MM-DD-YYYY'), 'DD /MM/YYYY')

 Regarts, Matheus

[Dataintellinalytics]

If your line number 7 in Main Tab is SET DateFormat='MM/DD/YYYY';  then first one should work

Date(Date#([Payments Thru],'MM-DD-YYYY')) 

OR try second one

Date(Date#([Payments Thru],'MM-DD-YYYY'),'MM/DD/YYYY'')

Refer to this, it might help.

https://youtu.be/R9iwC9QgCIQ

[qv_testing]

try this

Date([Payements by],'MM/DD/YYYY')

or

Date(date#([Payements by],'MM-DD-YYYY'),'MM/DD/YYYY')

[jmurrevcyc]

I have confirmed the DateFormat = 'M/D/YYYY' 

I tried changing it and all date fields to be as 'MM/DD/YYYY' to no avail.

Then I went back into the workbook and replaced the values manually, which is the manual fix I have been doing all along. And the data populated in the dashboards as expected. Then I looked at the format of the date and it was being read as 'YYYY/MM/DD'

.So then I changed the format of all Dates to be that including the replace formulas. Still did not get the desired outcome. I did see where the date field was no coming in formatted looking like a date, but dashboards reflecting this metric are still blank. 

Thanks for all of the suggestions. Not sure why they aren't working.

[jmurrevcyc]

Neither of these worked for some reason. Thank you for the suggestions. I did confirm the dateformat was set to 'M/D/YYYY' . So I tried to play with that to no avail .

[marcus_sommer]

By "normal" date-structures no replace() will be needed else the date(date#(MyField, 'FormatPattern')) conversion + formatting will do the job. Important for it is that the interpretation variables are set suitable - and you could set them multiple times within your script. For example by starting with the company-defaults and if any load needs a special treatment you could set an appropriate adjustment before and afterwards you changed it back to the defaults.

If it hasn't worked for you it just means you hadn't applied the right Format-Pattern - at least in regard to real existing values which may not those which you sees in Excel. They may look like a formatted date but may a string and/or containing any spaces or special chars or be just stored as pure numbers and/or your date might be not a date else a timestamp.

I think I would at first load this field with text() to prevent any interpretation on the Qlik-side and looking on the values within a table-box. I could imagine that the values are then looking differently from your expectation.