topic Re: Creating a date out of a string with odd format in QlikView

Creating a date out of a string with odd format

Thu, 06 Sep 2012 09:16:02 GMT

I've got a date type field in format 'MMMMM dd, YYYY HH:MM' and I need to make something sensible out of it, that will still sort correctly as months.

E.g. March 8, 2012 09:25 or March 27, 2012 10:47

At this stage when I "extract" the month, for example, by using left(Date,index(Date,' ')) as Month, I get the month I want, but it then sorts in an Alphabetic way, not in month sequence. I need all the data found in this field, each in their own format.

ANY help would be appreciated.

Re: Creating a date out of a string with odd format

Jason_Michaelides — Thu, 06 Sep 2012 09:21:06 GMT

Try:

Date(Date#(YourDateField,'MMMM dd, YYYY hh:mm'),'MMMM dd, YYYY hh:mm') AS Date,

Month(Date#(YourDateField,'MMMM dd, YYYY hh:mm')) AS Month,

in your script.

Hope this helps,

Jason

Re: Creating a date out of a string with odd format

Thu, 06 Sep 2012 09:30:42 GMT

Hi Jason,

No luck - this is my code:

LOAD

Date,
          Date(Date#(Date,'MMMM dd, YYYY hh:mm'),'MMMM dd, YYYY hh:mm') AS MyDate,
          Month(Date#(Date,'MMMM dd, YYYY hh:mm')) AS MyMonth,
          left(Date,index(Date,',')) as MonthDay

and this is the result:

Re: Creating a date out of a string with odd format

Jason_Michaelides — Thu, 06 Sep 2012 10:06:10 GMT

Please provide some sample data.

Re: Creating a date out of a string with odd format

Thu, 06 Sep 2012 10:10:32 GMT

Input is out of a CSV file - here's a snippet:

Date;Item;Destination;Type;Rate;Duration;Amount;

"March 31, 2012 21:26";+27999999999;South Africa-Cape Town;Call;0.000;00:04;EUR 0.000;

"March 31, 2012 15:03";+27999999999;South Africa-Cape Town;Call;0.000;00:00;EUR 0.000;

"March 31, 2012 09:00";+27999999999;South Africa-Cape Town;Call;0.000;00:03;EUR 0.000;

"March 31, 2012 08:41";+27999999999;South Africa-Cape Town;Call;0.000;00:05;EUR 0.000;

"March 31, 2012 08:30";+27999999999;South Africa-Cape Town;Call;0.000;10:25;EUR 0.000;

"March 31, 2012 08:25";+27999999999;South Africa-Cape Town;Call;0.000;03:12;EUR 0.000;

"March 30, 2012 19:30";+27999999999;South Africa-Cape Town;Call;0.000;17:48;EUR 0.000;

"March 27, 2012 10:47";+27999999999;South Africa-Cape Town;Call;0.000;00:27;EUR 0.000;

"March 20, 2012 19:35";+27999999999;South Africa - Mobile;Call;0.195;09:52;EUR 2.039;

"March 20, 2012 19:30";+27999999999;South Africa - Mobile;Call;0.195;05:26;EUR 1.259;

"March 20, 2012 19:29";+27999999999;South Africa - Mobile;Call;0.195;00:00;EUR 0.000;

"March 19, 2012 13:25";+27999999999;South Africa-Cape Town;Call;0.000;10:54;EUR 0.000;

I've anonomised the phone numbers.

Re: Creating a date out of a string with odd format

Jason_Michaelides — Thu, 06 Sep 2012 10:19:22 GMT

Here you go - hope it helps,

Jason

Re: Creating a date out of a string with odd format

Thu, 06 Sep 2012 10:23:07 GMT

Call the dogs off, the hunt is over!

Thanx Jason - much appreciated.

Re: Creating a date out of a string with odd format

Jason_Michaelides — Thu, 06 Sep 2012 10:27:46 GMT

Pleasure.

Re: Creating a date out of a string with odd format

Thu, 13 Sep 2012 09:38:02 GMT

Hi Jason,

a few days has gone by, and I've since realised that there is another issue with this. After you replied to my problem, you sent me a qvw that I then continued working on - however, when I try to apply your solution in my own (or another new) qvw, I run into the same problems again - I think the difference is caused by some (by me) unnoticed setting that you and I have different values for.

Can I send you MY qvw, and input, so we're looking at exactly the same thing?

Re: Creating a date out of a string with odd format

Jason_Michaelides — Thu, 13 Sep 2012 09:39:52 GMT

OK - I'll look at it when I get a moment.