Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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";
Hi Fei Xu,
I am facing the following error now:
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";
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";
Thanks, it seems to work!