Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Error in Concatenation

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";

         


Screenshot (32).png

1 Solution

Accepted Solutions
NZFei
Partner - Specialist
Partner - Specialist

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";

View solution in original post

13 Replies
Thiago_Justen_

You should to create a lib connection with your path:

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



Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
sasiparupudi1
Master III
Master III


may be  replace

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

to

Load *

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

Anonymous
Not applicable
Author

Hi,

I'm getting the below error now:

Screenshot (33).png

sasiparupudi1
Master III
Master III

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";

        

Anonymous
Not applicable
Author

Hi Sasidhar,

It still gives me the same error 😕

The following error occurred:

Field 'full_name' not found

The error occurred here:

?

sasiparupudi1
Master III
Master III

Please paste your new script here and if possible, the log file

Anonymous
Not applicable
Author

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";

         

Anonymous
Not applicable
Author

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.

NZFei
Partner - Specialist
Partner - Specialist

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$);