Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shimon_klick
New Contributor

JOINing Access and Excel database

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,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

        

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

         

Screenshot (33).png

4 Replies
thiago_justen
Valued Contributor III

Re: JOINing Access and Excel database

Please, take care of duplicates threads.

Error in Concatenation

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
shimon_klick
New Contributor

Re: JOINing Access and Excel database

Can you please kindly elaborate?

thiago_justen
Valued Contributor III

Re: JOINing Access and Excel database

Sorry, but I wasn't rude. What I mean is if this thread is with the same subject of the others, maybe you should keep only one open.

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
shimon_klick
New Contributor

Re: JOINing Access and Excel database

Oh Sorry, I get what you meant now.

Initially they were two different problems, but now it seems to have been narrowed down to one.

Can you please look into this:

https://community.qlik.com/message/1468536?et=watches.email.thread#1468536

Community Browser