Make Calendar with 001 and 2012

[#1]

Hi, from our BW, our months and years roll out in the following format :

FMonth = 001

FYear = 2012

Now, based on that, I want to build my calendar, but somehow it doesn't work with the following code :

LOAD Distinct

     FYear,

     FMonth,

     MakeDate(Num(FYear), Num(Mid(FMonth,2,2),1) as TempDate,

     Date#(TempDate,'YYMM') as OrderDate

Resident Orders;

What do i do wrong?

Thanks

[#2 · Accepted solution]

Hi

You have a couple of issues but the main one is that you can't create the field TempDate and use it to create OrderDate in the same preceeding load. You are also trying to use the date#() function to format the date which it doesn't do. date#() creates a date from a string following the given format. Your makedate() already creates a valid date and so you simply need to use the date() function to format it rather than the date#() function.

Assuming you don't want TempDate in your final table, try the below code instead:

LOAD DISTINCT

     FYear,

     FMonth,

     date(makedate(num(FYear),num(FMonth,'00')),'YYMM') AS OrderDate;

Resident Orders;

Hope that makes sense

Matt

[CELAMBARASAN]

Hi,

     Simply use

LOAD DISTINCT

     FYear,

     FMonth,

     makedate(num(FYear),num(FMonth,'00')) AS OrderDate

Resident Orders;

Celambarasan

[#4]

thanks guys