Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am receiving the Script Error below when running this code -
PeriodTable: //creating a table containing all the dates
Load Distinct
Entity,
Date,
MonthYear as Period
resident Final_Table
AsOfPeriodTable: // Creating Current and Rolling 12 period type
Load
Entity,
Date,
Period as AsOfPeriod,
'Current' as PeriodType,
Period as MonthYear
Resident PeriodTable;
Concatenate (AsOfPeriodTable)
LOAD
Entity,
Date,
Period as AsOfPeriod,
'Rolling 12' as PeriodType,
date(AddMonths(Period,1-IterNo()),'MMM-YYYY') as MonthYear
Resident PeriodTable
While IterNo() <= 12;
Inner Join(AsOfPeriodTable)
LOAD Period as MonthYear
Resident PeriodTable;
DROP Table PeriodTable;
AsOfPeriodTable2: // to get separate Month and Year
Load
Entity as Entity,
AsOfPeriod,
Date as DateF,
date(date#(Date,'DD-MMM-YYYY'),'YYYY-MM-DD') as DateF2,
Year2date(AsOfPeriod,0,1,$(varMaxDate))*-1 as CurYTDFlag,
if(AsOfPeriod<=AddMonths(MonthEnd($(varMaxDate)),-12),Year2Date(AsOfPeriod,-1,1,$(varMaxDate))*-1,0) as LastYTDFlag,
month(AsOfPeriod) as MonthAsOf,
year(AsOfPeriod) as YearAsOf,
PeriodType,
MonthYear
Resident AsOfPeriodTable;
DROP Table AsOfPeriodTable;
Does any one know what this error is suggesting and how I get this to work?
Thanks
The error I was receiving was due to the fact that Entity, Date and MonthYear did not appear in all the data sets. Therefore the PeriodTable could not be created and this error occured.
try putting $(varMaxDate) in single quotes:
if(AsOfPeriod<=AddMonths(MonthEnd('$(varMaxDate)'),-12),Year2Date(AsOfPeriod,-1,1,$(varMaxDate))*-1,0) as LastYTDFlag,
The error I was receiving was due to the fact that Entity, Date and MonthYear did not appear in all the data sets. Therefore the PeriodTable could not be created and this error occured.