Script loading invalid expression

[Anonymous]

Hi all,

I'm practising script. This one works:

Directory;
Maintable:
LOAD date(date#(SalesDate,'MM/DD/YYYY'),'DD-MM-YYYY') as SalesDate, 
     SalesVolume
FROM
practicecalendar.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

MasterCalendar:
Load
Date(SalesDate) as SalesDate,
Year(SalesDate) as Year,
'Q'&ceil(month(SalesDate)/3) as Quarter,
Day(SalesDate) as Day,
Week(SalesDate) as Week,
Month(SalesDate) as Month,
Resident Maintable;

But then I created two additional fields and get the error that the expression is invalid.

Min(SalesDate) as MinDate,

Max(SalesDate) as MaxDate

Directory;
Maintable:
LOAD date(date#(SalesDate,'MM/DD/YYYY'),'DD-MM-YYYY') as SalesDate, 
     SalesVolume
FROM
practicecalendar.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

MasterCalendar:
Load
Date(SalesDate) as SalesDate,
Year(SalesDate) as Year,
'Q'&ceil(month(SalesDate)/3) as Quarter,
Day(SalesDate) as Day,
Week(SalesDate) as Week,
Month(SalesDate) as Month,
Min(SalesDate) as MinDate,
Max(SalesDate) as MaxDate
Resident Maintable;

Can anyone please help explain the reason? And how to detect the cause so that next time I can get it right?

Thank you.

[Anil_Babu_Samineni · Accepted solution]

Try this way?

Maintable: 

LOAD date(date#(SalesDate,'MM/DD/YYYY'),'DD-MM-YYYY') as SalesDate, 

    SalesVolume 

FROM 

practicecalendar.csv 

(txt, codepage is 1252, embedded labels, delimiter is ',', msq); 

MasterCalendar: 

Load 

Date(SalesDate) as SalesDate, 

Year(SalesDate) as Year, 

'Q'&ceil(month(SalesDate)/3) as Quarter, 

Day(SalesDate) as Day, 

Week(SalesDate) as Week, 

Month(SalesDate) as Month

Resident Maintable;

Load Min(SalesDate) as MinDate, 

Max(SalesDate) as MaxDate 

Resident Maintable;

But, I really didn't understand why you need Min and Max Date if you are not using anywhere As of Now?

[Anonymous]

Many thanks Anil. Being a novice, can I understand why do we need to separating it into 2 Loads with 2 resident Maintable? In my simple mind, Min/Max is just like other above functions.

[Anil_Babu_Samineni]

I am creating Min and Max dates from Maintable using SalesDate. Then, We can create separate table for Calendar to get associated with All fact fields into Dimension tables and Fields from calendar.