topic AW:Date conversion problem in QlikView

Date conversion problem

Thu, 16 Dec 2010 17:25:10 GMT

I am trying to find a solution for the following problem:

I receive a production planning date in a format "MONTHNAME YYYY" - that tells me that a certain material has to be produced let's say in "JULY 2011". Is there a way to generate a date out of this format?

I was able to add a column via Access that the splits this information into two separate fields "07" and "2011", but I found no way to show this information in two listboxes as "July" and "2011". Needless to say that the month name should be in the correct order.

I also would like to make then a selection of the months that belong to our business year (April-March) and one to the calendar year (January-December).

BTW: I'm using a german version of QlikView.

I really appreciate all hints I can get.

Many thanks!

Günter

AW:Date conversion problem

brenner_martina — Thu, 16 Dec 2010 17:49:50 GMT

Hi, Guenter

yes, there is a solution:

Date#(Field,'MMMM YYYY') as Datefield

AW:Date conversion problem

Thu, 16 Dec 2010 20:26:39 GMT

Hi Martina,

thanks for the quick reply. Used the following import script according to your proposal:

LOAD A,
Date#(B,'MMMM YYYY') as Datefield,
C,

A is the department, B in this case is the field with the date "January 2011", C blah, blah,

The import works fine - the box is named Datefield, but the months still show up in a wrong order, ie. February, January, March. Is there something else I need to do in the settings somwhere?

best regards
Günter

AW:Date conversion problem

Anonymous — Thu, 16 Dec 2010 21:00:12 GMT

Months are dual, each has text value (Jan) and numeric value (1). If you sort by num numeric value, it should work.

AW:Date conversion problem

Fri, 17 Dec 2010 08:03:13 GMT

So far I d not get ahead...

The list box is still not listed correctly nor does the field work anymore as it was intended i.e. clicking on a month should bring only the activities in this month.

The import string looks like this now: LOAD
Department,
Date#([Revenue month],'MMMM YYYY')as Revenue_month,
[Production month],
[Item description],
Country,

As I was able to get also one separate field with the month number (sorted correctly) I think it might be easier to show these numbers as written month name. How would this work?

Günter

AW:Date conversion problem

johnw — Sat, 18 Dec 2010 02:28:52 GMT

You might need to do this:

date(date#([Revenue month],'MMMM YYYY'),'MMMM YYYY') as Revenue_month,

I always wrap date#() in date() when reading in fields. It's been so long I can't even remember what happens if I don't.