IMP Info Regarding Mapping load
- The mapping table must have only two columns
- The first column is always the ID and second column always the value
- The column names in the mapping table are not relevant
- The mapping table only exists during load
- No associations are made by column name
- Getting the table name right is essential
- No error is raised if you use a mapping table name that doesn’t exist
- It is easy to miss the MAPPING prefix on the load – check this before reload!
** Mapping with Excel File **
** Script Area**
Emp:
Mapping
LOAD emp_id,
name
FROM
(ooxml, embedded labels, table is Sheet1);
Detail:
LOAD emp_id,
Salary,
Dept,
ApplyMap('Emp',emp_id) as Emp_name
FROM
(ooxml, embedded labels, table is Sheet2);
** Sheet Area**
** Mapping with DATA BASE File **
** Script Area**
Emp:
Mapping
LOAD "DEPARTMENT_ID",
"FIRST_NAME";
SQL SELECT "DEPARTMENT_ID",
"FIRST_NAME"
FROM HR.EMPLOYEES;
LOAD "DEPARTMENT_ID",
"DEPARTMENT_NAME",
"LOCATION_ID",
ApplyMap('Emp',DEPARTMENT_ID) as name;
SQL SELECT "DEPARTMENT_ID",
"DEPARTMENT_NAME",
"LOCATION_ID"
FROM HR.DEPARTMENTS;
** Sheet Area**
** Table Viewer **
- Create a mapping table using these two tables?
** Script Area**
Category:
Mapping
LOAD CategoryId,
// CategoryName,
// SubCategoryId,
CategoryOwner
FROM
(ooxml, embedded labels, table is Category);
SubCategory:
LOAD SubCategoryId,
CategoryId,
SubCategpryName,
Quantity,
ApplyMap('Category',CategoryId) as Cat_Owner
FROM
(ooxml, embedded labels, table is SubCategory);
** Sheet Area**
** Table Viewer **
- Create one inline table and map with above tables( One mapping table and use apply map in two other tables) ?
** Script Area**
Emp:
Mapping
LOAD * INLINE [
Emp_id, Name
1, Sushil
2, Santosh
3, Sheetal
4, Dhananjay
5, Aditi
];
Detail:
LOAD *,ApplyMap('Emp',Emp_id) as new_name INLINE [
Emp_id, Salary, Dept
1, 5686, HR
2, 4856, PHP
3, 7896, BI
4, 4586, .NET
5, 7586, C++
];
** Sheet Area**
** Table Viewer **
- Give more than one field (possible number of fields) at apply map function?
** Script Area**
Orders:
Mapping
LOAD OrderID,
OrderDate
// CustomerID,
//EmployeeID,
//ShipperID,
//Freight
FROM
(ooxml, embedded labels, table is Orders);
Order_Details:
LOAD OrderID,
LineNo,
ProductID,
Quantity,
UnitPrice,
Discount,
ApplyMap('Orders',OrderID) as Orders
FROM
(ooxml, embedded labels, table is [Order Details]);
** Sheet Area**
** Table Viewer **
- Create a keep () table out of these tables?
** Script Area**
Category:
LOAD CategoryId,
CategoryName,
SubCategoryId,
CategoryOwner
FROM
(ooxml, embedded labels, table is Category);
SubCategory:
inner Keep Category:
LOAD //SubCategoryId,
CategoryId,
SubCategpryName,
Quantity
FROM
(ooxml, embedded labels, table is SubCategory);
** Sheet Area**
** Table Viewer **
- Create the following data model using Keep from the Employee, Office, Categories ?
** Script Area**
Employee:
LOAD EmpName,
EmpNo,
DeptName,
DeptNo,
Office,
Salary
FROM
(ooxml, embedded labels, table is Employee);
Office:
Inner keep Employee:
LOAD //Office,
EmpName as DeptName ,
OfficeLoc,
Area,
OfficeCategory
FROM
(ooxml, embedded labels, table is Office);
Category:
Join Employee :
LOAD OfficeCategory,
CategoryId,
// CategoryCode,
CategoryName
FROM
(ooxml, embedded labels, table is Category);
Nested Applymap()
A:
mapping LOAD * INLINE [
emp_id, emp_name
1, niraj
2, sachin
3, Dhananjay
4, Ganesh
5, abhi
6, sachin
];
B:
mapping LOAD * INLINE [
emp_name, loc
niraj, Pune
sachin, Mumbai
swapnil, Delhi
rahul, Banglore
abhi, Delhi
satish, Pune
];
LOAD *,ApplyMap('B',ApplyMap('A',5,'no_data'),'No Data') as New INLINE [
dept_id, dept_name, loc, emp_name
111, IT, Pune, niraj
222, HR, Mumbai, sachin
333, Marketing, Delhi, swapnil
444, Sales, Banglore, rahul
555, Others, Delhi, abhi
666, Health, Pune, satish
];
Hope this will help you to understand what apply map in qlikview
