How to store year and month in separate columns

[#1]

Hi,

i have the following date column and i need to store the year and month in seperate column through. How can i achieve that.

Please let me know.

load * , date(Date#(date, 'MM/DD/YYYY'),'MM/DD/YYYY')as NewDate Inline [

date

01/01/2014,

02/01/2014,

01/02/2015,

04/02/2015];

i need to achieve the above by the do while loop only.

result to achieve- Month, Year

                          01, 2014

                         02,2014

                        01,2015

                        04, 2015

Thanks,

Vasudha

[sunny_talwar · Accepted solution]

Why do you need to loop? Try this may be:

LOAD *,

          Month(NewDate) as Month,

          Year(NewDate) as Year,

          Date(NewDate, 'MM, YYYY') as NewDateDifferentFormat;

LOAD * ,

          Date(Date#(date, 'MM/DD/YYYY'),'MM/DD/YYYY') as NewDate

Inline [

date

01/01/2014,

02/01/2014,

01/02/2015,

04/02/2015];

[maxgro]

SET DateFormat='MM/DD/YYYY';

// no loop

z:

load *, Date(MonthStart(NewDate), 'MM,YYYY') as MonthYear;

load *, date(Date#(date, 'MM/DD/YYYY'),'MM/DD/YYYY')as NewDate Inline [

date

01/01/2014,

02/01/2014,

01/02/2015,

04/02/2015

]

;

// loop

FOR i=0 to NoOfRows('z')

  let vdate = Peek('NewDate', $(i));

  let vmonthyear= Date(MonthStart('$(vdate)'), 'MM,YYYY');

  trace $(vdate);

  trace $(vmonthyear);

NEXT;

[Anonymous]

Temp:

load * , date(Date#(date, 'MM/DD/YYYY'),'MM/DD/YYYY')as NewDate Inline [

date

01/01/2014,

02/01/2014,

01/02/2015,

04/02/2015];

TEST:

LOAD

NewDate,

Date(NewDate, 'MM, YYYY') as [Month-Year]

Resident Temp;

DROP Table Temp;

[MarcoWedel]

Hi,

another solution without loop (I don't see a reason why you could need one):

LOAD *,
    Num(Month(date),'00') as Month,
    Year(date) as Year;
LOAD Date#(date, 'MM/DD/YYYY')as date Inline [
    date
    01/01/2014
    02/01/2014
    01/02/2015
    04/02/2015
];

hope this helps

regards

Marco