topic Script error in QlikView

Script error

nareshthavidishetty — Tue, 01 Mar 2016 14:47:52 GMT

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;

Re: Script error

sunny_talwar — Tue, 01 Mar 2016 14:53:20 GMT

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

Re: Script error

Anonymous — Tue, 01 Mar 2016 14:54:48 GMT

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;

Re: Script error

puttemans — Tue, 01 Mar 2016 14:56:18 GMT

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