Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
You can try MakeDate(subfield(FieldName,'.',2) , subfield(FieldName,'.',1)
use the date # function
Date#(right(fieldname,7),'MM.YYYY')
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
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
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.
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
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 ;
Hi,
You can also try like this
Load
MakeDate(SubField(DATAEGV_PERIODE, '.', -1), SubField(DATAEGV_PERIODE, '.', 1) AS Date,
*
Regards,
jagan.
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