What is ApplyMap()

Tue, 11 Feb 2014 06:30:36 GMT

What is ApplyMap()

Re: What is ApplyMap()

Colin-Albert — Tue, 11 Feb 2014 06:34:35 GMT

Have a look at this blog on applymap

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Re: What is ApplyMap()

sujeetsingh — Tue, 11 Feb 2014 06:37:46 GMT

ApplyMap() is the mapping function :

go to this blog for detailed use and syntaxes

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Re: What is ApplyMap()

Tue, 11 Feb 2014 06:41:32 GMT

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

Re: What is ApplyMap()

Tue, 11 Feb 2014 07:15:32 GMT

Go to this link

http://www.learnqlickview.com/a-qlikview-tutorial-mapping-load-in-qlikview/

hope this will help you to understand what apply map is

Re: What is ApplyMap()

ngulliver — Tue, 11 Feb 2014 09:52:04 GMT

Hi, Narsing Rao.

In addition to Colin's useful link, have a look at Steve Dark's blog on the subject:

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

It will show you what you can do with an applymap.

Regards,

Neil

Re: What is ApplyMap()

Nagaraju_KCS — Wed, 05 Nov 2014 09:49:44 GMT

Hi Rao,

ApplyMap is used to mapping any expression to a previously loaded Mapping table

Syntax:

ApplyMap(TableName, FieldName) as FieldName

Re: What is ApplyMap()

Wed, 05 Nov 2014 09:54:47 GMT

Try using help in qlikview desktop, there you will get description with example.

It is generally used to map values of one type to other type like to map country codes with their names.

Cheers

Saurabh

topic Re: What is ApplyMap() in QlikView

What is ApplyMap()

Re: What is ApplyMap()

Re: What is ApplyMap()

Re: What is ApplyMap()

Re: What is ApplyMap()

Re: What is ApplyMap()

Re: What is ApplyMap()

Re: What is ApplyMap()