Separating / sorting date strings

Wed, 01 Apr 2015 13:01:49 GMT

Hey everyone.

Currently i am having a problem with my string of dates.

Our system delivers an XML file every hour with hourly data build up as:

FRI 20 MAR 2015 08:00:00

The next hour it is:

FRI 20 MAR 2015 09:00:00

now the problem is that Qlikview automatically sorts on the first letter instead of the date and time as this is seen as TEXT.

So if i have data of Fri Sat Sun and Mon it shows as:

FRI 20 MAR 2015 23:00:00

MON 23 MAR 2015 00:00:00

MON 23 MAR 2015 01:00:00

MON 23 MAR 2015 02:00:00

MON 23 MAR 2015 03:00:00

MON 23 MAR 2015 04:00:00

MON 23 MAR 2015 05:00:00

MON 23 MAR 2015 06:00:00

MON 23 MAR 2015 07:00:00

MON 23 MAR 2015 08:00:00

MON 23 MAR 2015 09:00:00

MON 23 MAR 2015 10:00:00

MON 23 MAR 2015 11:00:00

MON 23 MAR 2015 12:00:00

MON 23 MAR 2015 13:00:00

MON 23 MAR 2015 14:00:00

SAT 21 MAR 2015 00:00:00

Of course i can separate the string with Left / Mid / Right in the script, and sort it again, but somehow it messes up calculations.

One of the main fields in the data is Revenue.

now each new day starts just after 3 am (closing time)

when we open at 10 it will say 0 revenue.

In the next hour we earn €50 and the XML of 11 am will say Revenue: €50.

If we however earn 20 in the next hour it says at 12 Revenue: €70.

In the report I want to show the hourly revenue, so i would do NewValue(70) - OldValue(50) = CurrentValue(20)

And then display Current Value, but because the Sorting is messed up it will use:

Mon 23 Mar 2015 00:00:00 as NewValue

and

Fri 20 Mar 2015 23:00:00 as OldValue

Anyone have any idea how i can fix this?

If more information is required please do not hesitate to ask.

Kind Regards.

Mike Cleven.

Re: Separating / sorting date strings

stjepan_stanic — Fri, 17 Apr 2015 12:28:09 GMT

You could do it like this:

date(date#(mid(From,5),'DD MMM YYYY hh:mm:ss'),'DD MM YYYY hh:mm:ss')