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.

[v_iyyappan]

Hi,

Try like this

[Sales Table]:

LOAD

Name,

Number,

Model,

Make,

orderdate,

Floor(Num(orderdate)) as orderdateNum

from

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

// MASTER CALENDER

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

min(orderdateNum) as minDate,

max(orderdateNum) as maxDate

Resident [Sales Table];

LET varMinDate = FieldValue('minDate', 1);

LET varMaxDate = FieldValue('maxDate', 1);

DROP Table Temp;

MasterCalendar:   

Date(orderdateNum, 'YYYYMMDD') as Orderdate

Year(orderdateNum) As Year,

Month(orderdateNum) As Month,

Day(orderdateNum) As Day,

week(orderdateNum) As Week,

YeartoDate(orderdateNum)*-1 as CurYTDFlag,

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

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

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

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

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

WeekDay(orderdateNum) as WeekDay

LOAD

    ($(varMinDate) + (RecNo()) - 1)  AS orderdateNum        

AUTOGENERATE ($(varMaxDate) - $(varMinDate) + 1);

Hope its help

Iyyappan

[userid128223]

Date# is working fine and it is create a new varible, I think reading it into master table is where the issue arises.

I can see the script running up till the point where in master calendar it tries to read new_orderdate and then it fails saying field not found.

when i create a little text box =new_orderdate, I can see the date. hence

1) date is being created fine.

2) master calender it fails at this point min(new_orderdate) as minDate

It seems to me that it is referencing issue.

[userid128223]

Incoming field is a text field in below format

YYYYMMDD

1) convert text date field into proper date field.  ie new_orderdate

2) call that date field into master calendar.  to create various date, month, year, day, weekday fields.

[MayilVahanan · Accepted solution]

HI

Please check the attached file.. Its work for me..

[userid128223]

Its weird I tried isolating this issue. by doing this. I get error new_orderdate field not found.

tempdate1:

Load orderdate,

     Date#(orderdate,'YYYYMMDD') as new_orderdate

     FROM ................

tempdate2:

load new_orderdate

resident tempdate1;

[MayilVahanan]

Hi

Please check once , Date() function and tel the result.. can you?

[MayilVahanan]

HI

Just now change the format YYYYMMDD as in text file and run it..

Please check the attached file..

[userid128223]

I figured it out. Thank you guys for all your help.

I had qualify function in my script that was messing things up.