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";
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";
You should to create a lib connection with your path:
"C:\WINDOWS\QWT.mdb"."Orders"
may be replace
FROM "C:\WINDOWS\QWT.mdb"."Orders";
to
Load *
From lib://c/WINDOWS/QWT.mdb"."Orders";
Hi,
I'm getting the below error now:
Orders:
LOAD *,
ApplyMap('MapEmpIDtofull_name', EmployeeID, null()) as Sales_Rep
FROM "C:\WINDOWS\QWT.mdb"."Orders";
should be
Orders:
LOAD *,
ApplyMap('MapEmpIDtofull_name', EmployeeID, null()) as Sales_Rep;
SQL Select *
From lib://c/WINDOWS/QWT.mdb"."Orders";
Hi Sasidhar,
It still gives me the same error 😕
The following error occurred:
Field 'full_name' not found
The error occurred here:
?
Please paste your new script here and if possible, the log file
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';
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";
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,full_name,
"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";
This is the log file:
2018-04-10 17:26:30 Execution started.
2018-04-10 17:26:30 Qlik Sense Server Version 12.108.6
2018-04-10 17:26:30 CPU Target x64
2018-04-10 17:26:30 Operating System Windows 10 Home (64 bit edition)
2018-04-10 17:26:30 Wow64 mode Not using Wow64
2018-04-10 17:26:30 Language 0409 English English
2018-04-10 17:26:30 Country USA United States United States
2018-04-10 17:26:30 MDAC Version 6.3.9600.16384
2018-04-10 17:26:30 MDAC Full Install Version 6.3.9600.16384
2018-04-10 17:26:30 PreferredCompression 2
2018-04-10 17:26:30 EnableParallelReload 1
2018-04-10 17:26:30 ParallelizeQvdLoads 1
2018-04-10 17:26:30 EnableFlushLog 0
2018-04-10 17:26:30 UserLogfileCharset 65001
2018-04-10 17:26:30 OdbcLoginTimeout -1
2018-04-10 17:26:30 OdbcConnectionTimeout -1
2018-04-10 17:26:30 LongestPossibleLine 1048576
2018-04-10 17:26:30 ScriptWantsDbWrite false
2018-04-10 17:26:30 ScriptWantsExe false
2018-04-10 17:26:30 ICU_FOR_CHARACTER_CLASSIFICATION true
2018-04-10 17:26:30 ICU_FOR_CODE_PAGES true
2018-04-10 17:26:30 ICU_FOR_COLLATION true
2018-04-10 17:26:30 ICU_FOR_LOCALE_INFO true
2018-04-10 17:26:30 ICU_FOR_SUPPORT_INFO true
2018-04-10 17:26:30 ICU_FOR_TIME_ZONE_INFO true
2018-04-10 17:26:30 LogFile CodePage Used: 65001
2018-04-10 17:26:30 UseMemoryFillPattern 1
2018-04-10 17:26:30 ScriptMode Standard (limits system access)
2018-04-10 17:26:30 SequentialThreadCount 14
2018-04-10 17:26:30 NumberOfCores (effective) 4
2018-04-10 17:26:30 ReloadCodebase Bnf
2018-04-10 17:26:30 Reload Executed By Personal\Me
2018-04-10 17:26:30 Process Executing Qlik Sense Server
2018-04-10 17:26:30 Process ID 19924
2018-04-10 17:26:30 Grpc version 3.0.0
2018-04-10 17:26:30 Protobuf version 3.2.0
2018-04-10 17:26:30 0002 SET ThousandSep=','
2018-04-10 17:26:30 0003 SET DecimalSep='.'
2018-04-10 17:26:30 0004 SET MoneyThousandSep=','
2018-04-10 17:26:30 0005 SET MoneyDecimalSep='.'
2018-04-10 17:26:30 0006 SET MoneyFormat='$#,##0.00;-$#,##0.00'
2018-04-10 17:26:30 0007 SET TimeFormat='h:mm:ss TT'
2018-04-10 17:26:30 0008 SET DateFormat='M/D/YYYY'
2018-04-10 17:26:30 0009 SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'
2018-04-10 17:26:30 0010 SET FirstWeekDay=6
2018-04-10 17:26:30 0011 SET BrokenWeeks=1
2018-04-10 17:26:30 0012 SET ReferenceDay=0
2018-04-10 17:26:30 0013 SET FirstMonthOfYear=1
2018-04-10 17:26:30 0014 SET CollationLocale='en-US'
2018-04-10 17:26:30 0015 SET CreateSearchIndexOnReload=1
2018-04-10 17:26:30 0016 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
2018-04-10 17:26:30 0017 SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December'
2018-04-10 17:26:30 0018 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
2018-04-10 17:26:30 0019 SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday'
2018-04-10 17:26:30 0023 LIB CONNECT TO 'MS Access ODBC 2.0'
2018-04-10 17:26:31 0035 REM *** ******
2018-04-10 17:26:31 0036 ******
2018-04-10 17:26:31 0037 ******
2018-04-10 17:26:31 0038 ******
2018-04-10 17:26:31 0039 ******
2018-04-10 17:26:31 0040 ******
2018-04-10 17:26:31 0041 ******
2018-04-10 17:26:31 0042 ******
2018-04-10 17:26:31 0043 ******
2018-04-10 17:26:31 0044 ***
2018-04-10 17:26:31 0047 SQL SELECT CustomerID,
2018-04-10 17:26:31 0048 CompanyName,
2018-04-10 17:26:31 0049 ContactName,
2018-04-10 17:26:31 0050 Address,
2018-04-10 17:26:31 0051 City,
2018-04-10 17:26:31 0052 StateProvince,
2018-04-10 17:26:31 0053 PostalCode,
2018-04-10 17:26:31 0054 Country,
2018-04-10 17:26:31 0055 Phone,
2018-04-10 17:26:31 0056 Fax
2018-04-10 17:26:31 0057 FROM "C:\WINDOWS\QWT.mdb"."Customers"
2018-04-10 17:26:32 10 fields found: CustomerID, CompanyName, ContactName, Address, City, StateProvince, PostalCode, Country, Phone, Fax,
2018-04-10 17:26:32 92 lines fetched
2018-04-10 17:26:32 0058 Loosen Table Customers
2018-04-10 17:26:32 0061 REM *** ******
2018-04-10 17:26:32 0062 ******
2018-04-10 17:26:32 0063 ******
2018-04-10 17:26:32 0064 ******
2018-04-10 17:26:32 0065 ***
2018-04-10 17:26:32 0066 SQL SELECT OrderID,
2018-04-10 17:26:32 0067 ProductID,
2018-04-10 17:26:32 0068 UnitPrice,
2018-04-10 17:26:32 0069 Quantity,
2018-04-10 17:26:32 0070 Discount
2018-04-10 17:26:32 0071 FROM "C:\WINDOWS\QWT.mdb"."Order Details"
2018-04-10 17:26:32 5 fields found: OrderID, ProductID, UnitPrice, Quantity, Discount,
2018-04-10 17:26:32 2,172 lines fetched
2018-04-10 17:26:32 0078 REM *** ******
2018-04-10 17:26:32 0079 ******
2018-04-10 17:26:32 0080 ******
2018-04-10 17:26:32 0081 ******
2018-04-10 17:26:32 0082 ******
2018-04-10 17:26:32 0083 ***
2018-04-10 17:26:32 0084 SQL SELECT OrderID,
2018-04-10 17:26:32 0085 CustomerID,
2018-04-10 17:26:32 0086 EmployeeID,
2018-04-10 17:26:32 0087 Freight,
2018-04-10 17:26:32 0088 OrderDate,
2018-04-10 17:26:32 0089 ShipperID
2018-04-10 17:26:32 0090 FROM "C:\WINDOWS\QWT.mdb"."Orders"
2018-04-10 17:26:32 6 fields found: OrderID, CustomerID, EmployeeID, Freight, OrderDate, ShipperID,
2018-04-10 17:26:32 836 lines fetched
2018-04-10 17:26:32 0092 Loosen Table Orders
2018-04-10 17:26:32 0094 REM *** ******
2018-04-10 17:26:32 0095 ******
2018-04-10 17:26:32 0096 ******
2018-04-10 17:26:32 0097 ******
2018-04-10 17:26:32 0098 ******
2018-04-10 17:26:32 0099 ******
2018-04-10 17:26:32 0100 ******
2018-04-10 17:26:32 0101 ******
2018-04-10 17:26:32 0102 ***
2018-04-10 17:26:32 0103 SQL SELECT ProductID,
2018-04-10 17:26:32 0104 ProductName,
2018-04-10 17:26:32 0105 SupplierID,
2018-04-10 17:26:32 0106 CategoryID,
2018-04-10 17:26:32 0107 QuantityPerUnit,
2018-04-10 17:26:32 0108 UnitCost,
2018-04-10 17:26:32 0109 UnitPrice,
2018-04-10 17:26:32 0110 UnitsInStock,
2018-04-10 17:26:32 0111 UnitsOnOrder
2018-04-10 17:26:32 0112 FROM "C:\WINDOWS\QWT.mdb"."Products"
2018-04-10 17:26:32 9 fields found: ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitCost, UnitPrice, UnitsInStock, UnitsOnOrder,
2018-04-10 17:26:32 77 lines fetched
2018-04-10 17:26:32 0113 Loosen Table Products
2018-04-10 17:26:32 0116 LOAD
2018-04-10 17:26:32 0117 EmpID,
2018-04-10 17:26:32 0118 "Last Name",
2018-04-10 17:26:32 0119 "First Name",
2018-04-10 17:26:32 0120 Title,
2018-04-10 17:26:32 0121 "Hire Date",
2018-04-10 17:26:32 0122 Office,
2018-04-10 17:26:32 0123 "Extension",
2018-04-10 17:26:32 0124 "Reports To",
2018-04-10 17:26:32 0125 "Year Salary",
2018-04-10 17:26:32 0126 "First Name"&' '&"Last Name" as full_name
2018-04-10 17:26:32 0127 FROM [lib://C/EmpOff.xls]
2018-04-10 17:26:32 0128 (biff, embedded labels, table is Employee$)
2018-04-10 17:26:32 10 fields found: EmpID, Last Name, First Name, Title, Hire Date, Office, Extension, Reports To, Year Salary, full_name,
2018-04-10 17:26:32 45 lines fetched
2018-04-10 17:26:32 0131 MapEmpIDtofull_name:
2018-04-10 17:26:32 0132
2018-04-10 17:26:32 0133
2018-04-10 17:26:32 0134 Mapping LOAD EmpID,full_name,
2018-04-10 17:26:32 0135 "First Name"&' '&"Last Name" as full_name
2018-04-10 17:26:32 0136 FROM [lib://C/EmpOff.xls]
2018-04-10 17:26:32 0137 (biff, embedded labels, table is Employee$)
2018-04-10 17:26:32 Error: Field 'full_name' not found
2018-04-10 17:26:32 Execution Failed
2018-04-10 17:26:32 Execution finished.
Change the following mapping table:
MapEmpIDtofull_name:
Mapping LOAD EmpID,full_name,
"First Name"&' '&"Last Name" as full_name
FROM [lib://C/EmpOff.xls]
(biff, embedded labels, table is Employee$);
TO:
MapEmpIDtofull_name:
Mapping LOAD
EmpID,
"First Name"&' '&"Last Name" as full_name
FROM [lib://C/EmpOff.xls]
(biff, embedded labels, table is Employee$);