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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply