Date / Timestamp Formatting

[qliky88]

Back with another date formatting question 😞 

My data source is an Excel file within a Sharepoint list. I have a connection to the SharePoint which refreshes daily to reflect any changes. 

Within the Excel file there are 5 date columns, 3 of which I was successfully able to format. One of my columns [Authorised] is in a 'mm/dd/yyyy hh:mm' (the same as the 3 working columns) format within Excel, but when reading it in Qlik Sense any items within the column appear blank. This is the same for the one other remaining column. 

I've tried date# and timestamp# but to no success 😞 

any ideas would be greatly appreciated as always

[David_Fergen]

Hi Qliky88,

Thanks for asking the question! For Date and Timestamp have you tried looking over the guide here? It has a bunch of suggestions on Date and Time, and it might be able to help with your solution. 

Let me know if you have any other questions!

Thanks,

David

[qliky88]

Hi David, 

Yes I had explored the guide mentioned however I couldn't find anything that dealt with the issue I was having specifically 😞 

[qliky88]

@David_Fergen  is there a way to check what format the columns are currently in? Would this help with converting them over? 

[Primrose]

Have you tried something like Date([your field name],'MM/DD/YYYY HH:MM')?

[qliky88]

hi @Primrose 

=TIMESTAMP(alt(timestamp#([columnName],'DD/MM/YYYY hh:mm'),timestamp#(columnName,'DD/MM/YYYY hh:mm:ss')),'DD-MMM-YYYY hh:mm')

worked for one column, and for the other two

=date(date#(columnName, 'MM/DD/YYYY hh:mm'), 'DD-MMM-YYYY hh:mm')

seemed to work. 

I have tried variations of the above for the two remaining columns but can't figure out where I'm going wrong! 😞 

[Primrose]

Can you load the columns without any transformation and then play around on the front-end? You can create a straight table with one dimension (one column) and try different formats in the next columns. Try the one above, it works for me, but my field is already a timestamp. Also, if you display it in a table, you will see how Qlik interprets that field. Maybe Qlik can't detect it's a timestamp and interprets it as a string. You may use the date(string(filed,'field format')).

[qliky88]

Hey @Primrose  yes that is what I have been doing. When I load [Authorised] in to a straight table the value returned is blank (which was similar for the other 4 columns) unfortunately it looks like I've tried everything! 😂

[Primrose]

Haha, sorry I didn't understand that part. So I guess in the data model viewer, you don't have any values for that field? Can you load it with string() or text() functions? Or maybe you can use substring() and '&' to create the field in Qlik.

Also, this shouldn't be the case, but can you load the table from a server, not from the Sharepoint location? I highly doubt it would help, but it would be nice to exclude this.

[qliky88]

Yup, correct. In the DMV just calling the value on it's own down have any functions applied to it. It's unusual because before the app was published everything was pulling in ok, do you think it could be an issue on the SharePoint side? Maybe this is a question for my Qlik admin 😛