Date

[rdlccn70]

Hello,

I would like the following date format "001.2016" divided in month and year. -> 001 and 2016 or 1 and 2016

Name of the dimension is DATAEGV_PERIODE

The data comes in this form:

001.2016

002.2016

.....

012.2016

Does anyone have this idea?

Thanks in advance.

Erdal

[swuehl]

Something like

LOAD *,

          Month(YearMonth) as Month,

          Year(YearMonth) as Year;

LOAD *,

          MakeDate( Right(DATAEGV_PERIODE,4), Left(DATAEGV_PERIODE,3) ) as YearMonth;

LOAD DATAEGV_PERIODE

FROM YourTable;

[#3]

You can try MakeDate(subfield(FieldName,'.',2) , subfield(FieldName,'.',1)

use the date # function

Date#(right(fieldname,7),'MM.YYYY')

[rdlccn70]

Hi,

i have tried with this code:

LOAD DATAEGV_PERIODE

FROM

W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

LOAD MonthName(Left(DATAEGV_PERIODE,3)) as Month,

YearName(Right(DATAEGV_PERIODE,4)) as Year

FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

LOAD MakeDate( Right(DATAEGV_PERIODE,4), Left(DATAEGV_PERIODE,3) ) as YearMonth

FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

The output is confused:

Do you have any idea?

Thanks

Erdal

Von: swuehl

Gesendet: Mittwoch, 10. Februar 2016 18:08

An: Cücen, Erdal, NMM-FF <Erdal.Cuecen@S4M.COM>

Betreff: Re: - Date

[rdlccn70]

Hi,

it was confused, but it works with:

	Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Month,
	Date#(right(DATAEGV_PERIODE,4),'YYYY') asYear

Thanks alot.

Regards,

Erdal

[swuehl]

You've used

LOAD   MonthName(Left(DATAEGV_PERIODE,3)) as Month,

       YearName(Right(DATAEGV_PERIODE,4)) as Year

FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

Here, Monthname, and YearName functions expect a Date as argument, not the Year resp. Month value.

Check how I've first interpreted the date, then how I've created Year and Month from that date in a so called preceding load.

[PradeepReddy]

another way..

Month(Date#('001.2016','MM.YYYY')) as Month,

Year(Date#('001.2016','MM.YYYY')) as Year

replace '001.2016' with your date field

[avinashelite]

try like

For 001 & 2016 fromat:

LOAD

subfield(DATAEGV_PERIODE,'.',1) as Month,

subfield(DATAEGV_PERIODE,'.',2) as Year

from

table ;

For 01 & 2016 fromat:

LOAD

right(subfield(DATAEGV_PERIODE,'.',1),2) as Month,

subfield(DATAEGV_PERIODE,'.',2) as Year

from

table ;

[jagan]

Hi,

You can also try like this

Load

MakeDate(SubField(DATAEGV_PERIODE, '.', -1), SubField(DATAEGV_PERIODE, '.', 1) AS Date,

*

Regards,

jagan.

[#10]

Hi Erdal,

Try this,

Month(Date(Date#(DATAEGV_PERIODE,'0MM.YYYY'),'MM/DD/YYYY')) as Month,

Year(Date(Date#(DATAEGV_PERIODE,'0MM.YYYY'),'MM/DD/YYYY')) as Year

Regards