topic Re: month & year from date in QlikView

month & year from date

Wed, 28 Jan 2015 14:34:23 GMT

Hi Experts

Hoe can I script to receive a month and a year out of a field containing a date? I tried the following which did not work:

SQL SELECT jobnr,

datum,

Month (datum)& Year (datum) as StundenMonat,

stunden,

stundenart,

marnr as MitarbeiterStunden

FROM EASY.stunden;

I would like to have: 01 2014, 02 2014 e.g.

Thank you for your help!

Jan

Re: month & year from date

sunny_talwar — Wed, 28 Jan 2015 14:36:47 GMT

I wonder why it did not work. But Try this instead.

Month(Date(datum))& ' ' & Year(Date(datum)) as StundenMonat

This may or may not work, but worth giving a shot.

Best,

Re: month & year from date

agomes1971 — Wed, 28 Jan 2015 14:37:22 GMT

Hi,

maybe Month (datum)&' '& Year (datum) as StundenMonat

HTH

André Gomes

Re: month & year from date

kamal_sanguri — Wed, 28 Jan 2015 14:39:37 GMT

Hi,

You can use month and year function to take out the respective part from Date.

For Ex: if you want to pull number of the month you can try

numsum(month(DateField)) & Year (DateField)

Thanks,

kamal

Re: month & year from date

Wed, 28 Jan 2015 14:40:45 GMT

Check whether datum is in date format . If yes then try onth (datum)&' '& Year (datum)

Re: month & year from date

MarcoWedel — Wed, 28 Jan 2015 14:41:59 GMT

you're mixing QV script with SQL.

Try

LOAD *,

Month (datum)& Year (datum) as StundenMonat;

SQL SELECT jobnr,

datum,

stunden,

stundenart,

marnr as MitarbeiterStunden

FROM EASY.stunden;

Re: month & year from date

MarcoWedel — Wed, 28 Jan 2015 14:43:47 GMT

or better

LOAD *,

Date(MonthStart(datum),'MM YYYY') as StundenMonat;

SQL SELECT jobnr,

datum,

stunden,

stundenart,

marnr as MitarbeiterStunden

FROM EASY.stunden;

because this will create a real date instead of text, i.e. no sorting and calculation issues ...

regards

MArco

Re: month & year from date

sunny_talwar — Wed, 28 Jan 2015 14:44:53 GMT

Good catch. Did not see that.

Re: month & year from date

Wed, 28 Jan 2015 14:45:07 GMT

True Thank you!