previous article
arcplan offers a number of possibilities to set up a classic V-Lookup, as it is often used in Excel. One of these possibilities can be found under the blog entry Tips & Tricks: The Repeat Loop as Performance Killer.
The method described here works via the cross product of two tables and, as bulk operation, constitutes a “Left Outer Join”, while the intersection constitutes an “Inner Join”.
The V-Lookup in arcplan
Let’s assume that the data comes from two different data sources. In one of the sources, data about the unit and sales prices is located; in the other source, data about the order volumes is located.
In our example, the data comes from the SAP System; thus, the cross product is generated via the keys. This is mainly important if the titles of the products change or if they differ from the start.
Step 1
For the cross product from the keys, the information from the table “Key_1” and “Unit Price” is transformed into lines ([OBJ29] and [OBJ30]). In the table itself, the value is taken from line [OBJ30], while the contents in column [OBJ28] and line [OBJ29] remain the same. In case of inequality, the cell remains empty.
The result of the cross product may be merged into one column via the function MAX().
When using the function, please note that arcplan loads all information of the reports which are required for the display of the data on the report level into the memory. Thus, if multiple cross products are formed as shown in the picture above, the table contents are all loaded into the memory.
Step 2
In order to avoid this, the function can also be reproduced directly in a column. For this purpose, the functions of the pictures above are coordinated in one object.
Principally, however, the rule is: anything that can be already merged via the request should be merged there.
