Error in Concatenation

[Anonymous]

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LIB CONNECT TO 'MS Access ODBC 2.0';

//EXIT Script;

//vTodayMinusXMonths= date(addmonths(today(),-36),'YYYYMMDD')

//Load SubField(ProductName, ' ',2) As Department

//From "C:\WINDOWS\QWT.mdb"."Products";

//Customers:

//Load SubField(ProductName, ' ',2) As Department

//From "C:\Users\shimo\OneDrive\Documents\QWT.mdb"."Products";

REM LOAD CustomerID,

    CompanyName,

    ContactName,

    Address,

    City,

    StateProvince,

    PostalCode,

    Country,

    Phone,

    Fax;

SQL SELECT CustomerID,

    CompanyName,

    ContactName,

    Address,

    City,

    StateProvince,

    PostalCode,

    Country,

    Phone,

    Fax

FROM "C:\WINDOWS\QWT.mdb"."Customers";

Loosen Table Customers;

REM LOAD OrderID,

    ProductID,

    UnitPrice,

    Quantity,

    Discount;

SQL SELECT OrderID,

    ProductID,

    UnitPrice,

    Quantity,

    Discount

FROM "C:\WINDOWS\QWT.mdb"."Order Details";

//Loosen Table Order_Details;

//Let vCurrentMonth= Max(OrderDate);

//Let vdate = Addmonths(today(), -36,'MMM-YYYY');

//Date(MonthStart(OrderDate),'MMM-YY') as Month;

//Let formatmonth = monthname(OrderDate, -1);

REM LOAD OrderID,

    CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    ShipperID;

SQL SELECT OrderID,

    CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    ShipperID

FROM "C:\WINDOWS\QWT.mdb"."Orders";

//WHERE OrderDate>=$(vdate);

Loosen Table Orders;

REM LOAD ProductID,

    ProductName,

    SupplierID,

    CategoryID,

    QuantityPerUnit,

    UnitCost,

    UnitPrice,

    UnitsInStock,

    UnitsOnOrder;

SQL SELECT ProductID,

    ProductName,

    SupplierID,

    CategoryID,

    QuantityPerUnit,

    UnitCost,

    UnitPrice,

    UnitsInStock,

    UnitsOnOrder

FROM "C:\WINDOWS\QWT.mdb"."Products";

Loosen Table Products;

LOAD

    EmpID,

    "Last Name",

    "First Name",

    Title,

    "Hire Date",

    Office,

    "Extension",

    "Reports To",

    "Year Salary",

    "First Name"& ' ' & "Last Name" as full_name

FROM [lib://C/EmpOff.xls]

(biff, embedded labels, table is Employee$);

MapEmpIDtofull_name:

Mapping LOAD EmpID,

"First Name"& ' ' & "Last Name" as full_name

FROM [lib://C/EmpOff.xls]

(biff, embedded labels, table is Employee$);

LIB CONNECT TO 'MS Access ODBC 2.0';

Orders:

     LOAD *,

          ApplyMap('MapEmpIDtofull_name', EmployeeID, null()) as Sales_Rep

          FROM "C:\WINDOWS\QWT.mdb"."Orders";

[Anonymous]

Hi Fei Xu,

I am facing the following error now:

[Anonymous]

This is my code:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LIB CONNECT TO 'MS Access ODBC 2.0';

//EXIT Script;

//vTodayMinusXMonths= date(addmonths(today(),-36),'YYYYMMDD')

//Load SubField(ProductName, ' ',2) As Department

//From "C:\WINDOWS\QWT.mdb"."Products";

//Customers:

//Load SubField(ProductName, ' ',2) As Department

//From "C:\Users\shimo\OneDrive\Documents\QWT.mdb"."Products";

REM LOAD CustomerID,

    CompanyName,

    ContactName,

    Address,

    City,

    StateProvince,

    PostalCode,

    Country,

    Phone,

    Fax;

SQL SELECT CustomerID,

    CompanyName,

    ContactName,

    Address,

    City,

    StateProvince,

    PostalCode,

    Country,

    Phone,

    Fax

FROM "C:\WINDOWS\QWT.mdb"."Customers";

Loosen Table Customers;

REM LOAD OrderID,

    ProductID,

    UnitPrice,

    Quantity,

    Discount;

SQL SELECT OrderID,

    ProductID,

    UnitPrice,

    Quantity,

    Discount

FROM "C:\WINDOWS\QWT.mdb"."Order Details";

//Loosen Table Order_Details;

//Let vCurrentMonth= Max(OrderDate);

//Let vdate = Addmonths(today(), -36,'MMM-YYYY');

//Date(MonthStart(OrderDate),'MMM-YY') as Month;

//Let formatmonth = monthname(OrderDate, -1);

REM LOAD OrderID,

    CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    ShipperID;

SQL SELECT OrderID,

    CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    ShipperID

FROM "C:\WINDOWS\QWT.mdb"."Orders";

//WHERE OrderDate>=$(vdate);

Loosen Table Orders;

REM LOAD ProductID,

    ProductName,

    SupplierID,

    CategoryID,

    QuantityPerUnit,

    UnitCost,

    UnitPrice,

    UnitsInStock,

    UnitsOnOrder;

SQL SELECT ProductID,

    ProductName,

    SupplierID,

    CategoryID,

    QuantityPerUnit,

    UnitCost,

    UnitPrice,

    UnitsInStock,

    UnitsOnOrder

FROM "C:\WINDOWS\QWT.mdb"."Products";

Loosen Table Products;

LOAD

    EmpID,

    "Last Name",

    "First Name",

    Title,

    "Hire Date",

    Office,

    "Extension",

    "Reports To",

    "Year Salary",

    "First Name"&' '&"Last Name" as full_name

FROM [lib://C/EmpOff.xls]

(biff, embedded labels, table is Employee$);

MapEmpIDtofull_name:

Mapping LOAD

    EmpID,

"First Name"&' '&"Last Name" as full_name

FROM [lib://C/EmpOff.xls]

(biff, embedded labels, table is Employee$);

LIB CONNECT TO 'MS Access ODBC 2.0';

Orders:

     LOAD *,

       ApplyMap('MapEmpIDtofull_name', EmployeeID, null()) as Sales_Rep;

           SQL Select *

         From lib://c/WINDOWS/QWT.mdb"."Orders";

[NZFei · Accepted solution]

Change the last part of the script to:

LIB CONNECT TO 'MS Access ODBC 2.0';

Orders:

     LOAD *,

       ApplyMap('MapEmpIDtofull_name', EmployeeID, null()) as Sales_Rep;

           SQL Select *

        From "C:\WINDOWS\QWT.mdb"."Orders";

[Anonymous]

Thanks, it seems to work!