date

[userid128223]

I am reading data from the text file trying to create master calander for date that is being read as string.

Table Name:

LOAD

Name

Number

Model

Make

orderdate

Date( Date#( orderdate, 'YYYYMMDD'), 'YYYYMMDD') as new_orderdate, 

from

.........

However when I try using new_orderdate in master calander it gives me field not found error.

[MayilVahanan]

HI

Try like this

Date( orderdate, 'YYYYMMDD') as new_orderdate

May be its already in correct format..

[swuehl]

What do you mean with "using new_orderdate in master calander"?  Can you post your relevant code snippet in which you create the master calendar? You can only reference the field new_orderdate when this is part of the input source, i.e. for example when resident loading your above table. It is not just known in global context across tables.

Stefan

[#4]

I noticed there is a comma at the end of the new_orderdate alias. Might this be the reason you are getting an error?

orderdate

Date( Date#( orderdate, 'YYYYMMDD'), 'YYYYMMDD') as new_orderdate, 

from

[userid128223]

Thanks for all the answers, I think the issue is referencing as Swuehl points out. Here is it code in more detail.

[Sales Table]:

LOAD

Name,

Number,

Model,

Make,

orderdate,

Date( Date#( orderdate, 'YYYYMMDD'), 'YYYYMMDD') as new_orderdate

from

.........   (text file)

// MASTER CALENDER

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

               min(new_orderdate) as minDate,

               max(new_orderdate) as maxDate

Resident [Sales Table];

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

               TempDate AS OrderDate,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[MayilVahanan]

Hi

From table viewer, you can able to see the new_orderdate field value? if so, can u able to say any one value ?

[userid128223]

I get error while reloading hence it does not get to that point.

[MayilVahanan]

HI

I think, your script are correct.. pls do one thing, comment ur master calendar, load only sales table and find whether the new_orderdate field has value or not? from that we can conclude it. and also do one thing, use in text box like this = min(orderdate), if it gives value, that its already in correct format , don't want to apply date#() function..

Hope it helps

[userid128223]

Hi Mayil

I commeted the master calendar and was able to get the value for new_orderdate.

How can I pass this new_orderdate to master calander.

[MayilVahanan]

HI

Can you post a error screen shot..

Have  doubt, new_orderdate gives value means, ok .. use set instead of let.. and see in the variable, if it gives value means, new_orderdate is not a problem...

If possible, give the sample text file. will try n give the solution..