topic Re: Making a Year Month Column in QlikView

Making a Year Month Column

Fri, 05 Sep 2014 08:13:04 GMT

Hello, please can someone tell me how to make a year month column

I am bringing in a field from excel called Anticipated Launch date.

An example of a date being brought in is Apr-15

I want to create a new column with year month in it so would want the above to show as

2015-4

Please can someone help with this issue.

Thank you

Kind Regards

Re: Making a Year Month Column

simondachstr — Fri, 05 Sep 2014 08:15:29 GMT

Date(date#([Anticipated Launch date],'MMM-YY'),'YYYY-M)

Date# evaluates a string into a calendar format.

Date formats a calendar value.

Re: Making a Year Month Column

Fri, 05 Sep 2014 08:34:27 GMT

Thank you for your help Martin but I still have an issue with it. I am adding this date to a column with a date already in it. They look like the same format but one is right justified and one left:

2014-6

2014-5

2014-8

Please could you assist in getting them the same. Thanks

Re: Making a Year Month Column

MK_QSL — Fri, 05 Sep 2014 08:35:30 GMT

Load

[Launch Date],

Date(MonthStart(Date#([Launch Date],'MMM-YY')),'YYYY-M') as YearMonth

Inline

[

Launch Date

Apr-14

Apr-15

Jun-14

Jun-16

];

Re: Making a Year Month Column

simondachstr — Fri, 05 Sep 2014 08:37:05 GMT

Anything that is left aligned is probably formatted as text, right a aligned as number/date.

Make sure the column already with a date in it also has the correct calendar format.

Re: Making a Year Month Column

Anonymous — Fri, 05 Sep 2014 08:37:12 GMT

Looks like one is a string and not a numeric date, try something like :

Date(date#([YourDateField],'YYYY-M)','YYYY-M')

Re: Making a Year Month Column

simondachstr — Fri, 05 Sep 2014 08:37:34 GMT

Why MonthStart()?

Re: Making a Year Month Column

MK_QSL — Fri, 05 Sep 2014 08:47:28 GMT

Even without MonthStart() it will work...

Re: Making a Year Month Column

Fri, 05 Sep 2014 08:53:44 GMT

Hi Martin

The other date comes in like this

convert(varchar,year([PstngDate])) + '-' + convert(varchar,month([PstngDate])) as yearmonth

Im not sure how to convert this back to match the other one.

......... and apologies,,, im very new to all this!!

Re: Making a Year Month Column

Anonymous — Fri, 05 Sep 2014 09:06:34 GMT

Emma

Try something like

date([PstngDate] ,'YYYY-M') as yearmonth ,

Re: Making a Year Month Column

Fri, 05 Sep 2014 09:16:03 GMT

Unfortunately this is still not working

Re: Making a Year Month Column

Anonymous — Fri, 05 Sep 2014 09:17:31 GMT

Emma

What kind of not working ?

Re: Making a Year Month Column

jonathandienst — Fri, 05 Sep 2014 09:51:41 GMT

It seems you have two date formats in your data source. Use the Alt() statement to parse the date in two or more formats, like this:

Date(Alt(Date#([Launch Date],'MMM-YY'), Date#([Launch Date],'YYYY-M')),'YYYY-M') as YearMonth

The first Date# will match the MMM-YY fields, but the YYYY-M fields in your input will not match, so then the Alt() will try the second Date#. The Date() statement will ensure all the resulting dates display as YYYY-M.

HTH

Jonathan

Re: Re: Making a Year Month Column

MarcoWedel — Fri, 05 Sep 2014 10:57:58 GMT

either:

LOAD

Date(Date#(yearmonth ,'YYYY-M'),'YYYY-M') as yearmonth;

SQL SELECT

convert(varchar,year([PstngDate])) + '-' + convert(varchar,month([PstngDate])) as yearmonth

FROM yourdatabase;

or:

LOAD

Date(PstngDate,'YYYY-M') as yearmonth;

SQL SELECT

PstngDate

FROM yourdatabase;