Script error

[nareshthavidishetty]

Hi,

Below is the script used but when we reload it give the following error,

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

FOR EACH file in FileList('$(vFilePath)\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

SheetNames:

SQLtables;

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1

LET sheetNamez = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

'MIS' as Misflag ,

'Capital' as Capflag,

GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;

For Cal:

MinMax:

LOAD Min(GltPostingDate) as MinDate,

  Max(GltPostingDate) as MaxDate

Resident Data;

SET vFiscalYearStartMonth = 2;

LET vStartDate = Peek('MinDate');

LET vEndDate = Peek('MaxDate');

DROP Table MinMax;

FiscalCalendar:

LOAD *,

  Dual('Q' & Ceil(FIMonth/3), Ceil(FIMonth/3)) AS App_Quarter, // Fiscal Calendar Quarter

  Dual(Text(Date(MonthEnd(GltPostingDate), 'MMM')), FIMonth) AS GltPostingDate; // Fiscal Calendar Month Name

LOAD *,

  Year(GltPostingDate) AS Years, // Standard Calendar Year

  Month(GltPostingDate) AS Months,

   day(GltPostingDate) as App_Day,

  Date(MonthEnd(GltPostingDate), 'MMM') AS MonthName,  // Standard Calendar Month Name

  Dual('Q' & Ceil(Month(GltPostingDate)/3), Ceil(Month(GltPostingDate)/3)) AS Quarter,  // Standard Calendar Quarter

  Mod(Month(GltPostingDate) - $(vFiscalYearStartMonth), 12)+1 AS FIMonth,  // Fiscal Calendar Month

  YearName(GltPostingDate, 0, $(vFiscalYearStartMonth)) AS FIYear;  // Fiscal Calendar Year

LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS GltPostingDate,

  RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

[sunny_talwar · Accepted solution]

I think the issue is here:

FiscalCalendar:

LOAD *,

  Dual('Q' & Ceil(FIMonth/3), Ceil(FIMonth/3)) AS App_Quarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(GltPostingDate), 'MMM')), FIMonth) AS GltPostingDate; // Fiscal Calendar Month Name

LOAD *,

  Year(GltPostingDate) AS Years, // Standard Calendar Year

  Month(GltPostingDate) AS Months,

  day(GltPostingDate) as App_Day,

  Date(MonthEnd(GltPostingDate), 'MMM') AS MonthName,  // Standard Calendar Month Name

  Dual('Q' & Ceil(Month(GltPostingDate)/3), Ceil(Month(GltPostingDate)/3)) AS Quarter,  // Standard Calendar Quarter

  Mod(Month(GltPostingDate) - $(vFiscalYearStartMonth), 12)+1 AS FIMonth,  // Fiscal Calendar Month

  YearName(GltPostingDate, 0, $(vFiscalYearStartMonth)) AS FIYear;  // Fiscal Calendar Year

LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS GltPostingDate,

  RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Rename the top one to call it something else:

Dual(Text(Date(MonthEnd(GltPostingDate), 'MMM')), FIMonth) AS GltPostingDateMonthName; // Fiscal Calendar Month Name

[Anonymous]

you define twice the field GltPostingDate

the first load above load all fields (include GltPostingDate) and creates new field GltPostingDate

LOAD *,

  Dual('Q' & Ceil(FIMonth/3), Ceil(FIMonth/3)) AS App_Quarter, // Fiscal Calendar Quarter

  Dual(Text(Date(MonthEnd(GltPostingDate), 'MMM')), FIMonth) AS GltPostingDate; // Fiscal Calendar Month Name

LOAD *,

  Year(GltPostingDate) AS Years, // Standard Calendar Year

  Month(GltPostingDate) AS Months,

   day(GltPostingDate) as App_Day,

  Date(MonthEnd(GltPostingDate), 'MMM') AS MonthName,  // Standard Calendar Month Name

  Dual('Q' & Ceil(Month(GltPostingDate)/3), Ceil(Month(GltPostingDate)/3)) AS Quarter,  // Standard Calendar Quarter

  Mod(Month(GltPostingDate) - $(vFiscalYearStartMonth), 12)+1 AS FIMonth,  // Fiscal Calendar Month

  YearName(GltPostingDate, 0, $(vFiscalYearStartMonth)) AS FIYear;  // Fiscal Calendar Year

LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS GltPostingDate,

  RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

[puttemans]

is it possible you create 'Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS GltPostingDate' while this variable already exists in your data table? Try renaming it to e.g. GltPostingDate1