13 Replies Latest reply: Apr 10, 2018 10:34 PM by shimon Gowda RSS

    Error in Concatenation

    shimon Gowda

      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

        • Re: Error in Concatenation
          THIAGO TEIXEIRA

          You should to create a lib connection with your path:

           

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



          • Re: Error in Concatenation
            Sasidhar Parupudi


            may be  replace

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

             

            to

            Load *

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

             

              • Re: Error in Concatenation
                shimon Gowda

                Hi,

                I'm getting the below error now:

                Screenshot (33).png

                • Re: Error in Concatenation
                  Sasidhar Parupudi

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

                          

                    • Re: Error in Concatenation
                      shimon Gowda

                      Hi Sasidhar,

                      It still gives me the same error :/

                      The following error occurred:

                      Field 'full_name' not found

                      The error occurred here:

                      ?

                        • Re: Error in Concatenation
                          Sasidhar Parupudi

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

                            • Re: Error in Concatenation
                              shimon Gowda

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

                                       

                              • Re: Error in Concatenation
                                shimon Gowda

                                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.

                                  • Re: Error in Concatenation
                                    Fei Xu

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

                                      • Re: Error in Concatenation
                                        shimon Gowda

                                        Hi Fei Xu,

                                        I am facing the following error now:

                                         

                                        Screenshot (34).png

                                        • Re: Error in Concatenation
                                          shimon Gowda

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