Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
anyone explain mapping load and apply map with some examples
thanks in advance
hita
Hi Hita!
The MAPPING statement provides an alternative to the JOIN statement in a very specific scenario: when you want to replace a single key value with a value from a lookup (mapping) table.
We prefix ' Mapping' to the load statement to tell Qlikview that a table is a mapping table.
• It can only have two columns, the first being the lookup value and the second being the mapping value to return.
• It is a temporary table. At the end of the script, QlikView automatically removes the table from the data model.
The mapping table is called with the 'Applymap()' function
Sample Code:
#####################################
Map_Price: //Mapping Table
Mapping LOAD * INLINE [
Category, Price
Adult, 10
Child, 5
Student, 6
Senior, 6
LoyalMember, 5
];
Tickets:
LOAD * INLINE [
Name, Class
Ann, Student
David, Adult
Sara, Senior
Keith, Child
Tom, Adult
Dave, Student
Sam, Adult
Monica, LoyalMember
Ethan, Student
Rose, Child
];
Price:
Load
Name,
Class,
ApplyMap('Map_Price',Class) as TicketPrice //Here we use applymap to look up for the ticket prices
Resident
Tickets;
Drop Table Tickets;
#####################################################
feel free to ask for more details if needed.
Hi,
Check this example for Mapping load and Apply Map functions.
Mapping load is used for load the mapping table where as Apply Map is used for mapping the Mapped table to another table for more see the example below.
//Mapping Table
CustomerTable:
Mapping Load * Inline
[
CustomerID, Country
A, Germany
B, France
C, Italy
D, Poland
E, Spain
Z, UK
];
Sales:
Load *,
ApplyMap('CustomerTable', CustomerID, 'Unknown') as CustomerCountry
Inline
[
CustomerID, Month, Sales
A, Jan, 100
B, Jan, 200
C, Jan, 300
D, Feb, 150
E, Mar, 200
F, Apr, 100
A, May, 140
D, May, 80
E, May, 100
F, Jun, 150
];
Regards
Anand
you can find the answer in QlikView online help
The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:
applymap('mapname', expr [ , defaultexpr ] )
where:
mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.
expr is the expression, the result of which should be mapped.
defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.
Examples:
// Assume the following mapping table:
map1:
mapping load * inline [
x, y
1, one
2, two
3, three ] ;
ApplyMap ('map1', 2 ) returns ' two'
ApplyMap ('map1', 4 ) returns 4
ApplyMap ('map1', 5, 'xxx') returns 'xxx'
ApplyMap ('map1', 1, 'xxx') returns 'one'
ApplyMap ('map1', 5, null( ) ) returns NULL
ApplyMap ('map1', 3, null( ) ) returns 'three'