Creating a Look Up table in WEBI at Report Side.

[Former Member]

Hi,

Suppose that you want to correlate the data you have in a query with some external data. For instance you´re analyzing countries sales and want to correlate it with the Gini index., which is available here GINI index (World Bank estimate) | Data | Table

This is how I would address the situation :

My query has the following objects  [country] , [sales], and the table of Gini Index is (for simplification purposes, I´m using just three values : Brazil, Chile and Venezuela)

I get

Country	Gini Index
Brazil	52.9
Chile	50.5
Uruguay	41.9

What I´ll do is create two "arrays" : one for the country names and the other with the values of the Gini index.

An array is a set elements where each element can be accessed by an index.

So, [array] =  {"Brazil", "Chile", "Venezuela"} has three elements in which the first element is "Brazil", the second "Chile" an the third "Venezuela".

As BO doesn´t have an array type, I will use the following trick . If I concatenate the values of the countries I will get the text "BrazilChileVenezuela" ([arrayind]) , in which

   "Brazil" is the first country and so on.

But I can not, at BO side get the first element because I don´t know where the first element starts and where in ends in [arrindex] .

If I create an array in which all elements get the same Length, I´ll be abble to index them by it´s start and end position.

For instance, if a Fix the Length of the elements to 10, I will get the following text [arrindex]  = "Brazil    Chile     Venezuela ", now, given the position of a element (1,2 or 3) I´m abble to get the elements text by the formula [Text] = Substr([arrIndex]; ([index]-1)*10+1;10) where index is the index of elements,

In the same way, If I want to find the index of an element :

[index] = ((Pos([arrIndex];[Element]-1)/10)+1.

Using this technique I´ll build two arrays with the data of Gini index, the first one is [arrIndex] which we already built, and the other [arrLookUp] which is the concatenation of the Gini index values :

[arrLookUp] = "52.950.541.9"

This is done externally to WEBI.

Now, inside WEBI, create two measure variables ([arrIndex] and [arrLookUp]) , both with value = " ".

Create two entry field Input Controls each of them associated to each of the variables.

Create another measure variable  [index] = ((Pos([arrIndex];[country])-1)/10)+1 this will give me the index of each [country] in [arrIndex], now I´ll use this index to get the value of Ginis index in [arrLookUp]

[Gini Index] =  ToNumber(Substr([arrLookUp]; ([index]-1)*10+1;10) ;"00.0")

Set a table with [Country], [Sales] and [Gini Index], copy and paste the text of both "arrays" into the appropriate Input Control and hit okay.

Regards,

Rogerio