Formatting data with escape character?

[jonvitale]

Hello all,

I am interpreting timestamps from SurveyMonkey that look like this:

2018-02-13T21:03:38+00:00

The "T" and the "+00:00" seem to be constant. I see them on every timestamp.

To work with them properly I tried setting the timestamp format to:

'YYYY-MM-DDThh:mm:ss+00:00'

But, the "T" is being interpretted by Qlik as "A" or "P" for a.m. or PM.

When I do something like this:

TimeStamp(Max(MyDateField))

I get:

2018-02-13P08:40:53+00:00

So, is there a way to tell Qlik that the "T" in my timestamp format is not to be interpreted? Some sort of escape character? I also want to make sure that my outputted (max date) is in 24 hour time.

Thanks

[sunny_talwar]

Do you want to keep the T in your date or getting rid of it is an option? Because it might still work if you store your timestamp without the T?

Other alternative might be is to use Dual function where the text includes the T, but the numerical value doesn't

[jonvitale]

I want to keep the T because I'm going to make requests back to SurveyMonkey to get data after the max date that I've collected, so I need it in their format.

I suppose I could format the incoming text to remove the "T" with the replace function.

[sunny_talwar · Accepted solution]

Then I guess you can go with the Dual function and use MaxString instead of Max to find the Max Date... it might become challenging to use it in set analysis to I recommend having a second field without T so that QlikView can use the new field, while SurveyMonkey can use the existing field....