Using Filtered Rows instead of VLOOKUP
July 2, 2010 by: David LaiThis article talks about using the filtered rows technique instead of a VLOOKUP.
Not until Xcelsius 2008 came out, were we able to filter on multiple rows of data that matched 1 entry. So in order to accomplish this we would either have to use VLOOKUP or INDEX MATCH. Using this technique was very cumbersome and although filtered rows has been available for a while, I have been on a couple engagements where I still see VLOOKUPS in the excel spreadsheet!
Thus hopefully writing this article will get the practice of using filtered rows ingrained in the
Xcelsius Developers minds.
I’ve created a video below that shows 2 scenarios below using VLOOKUP and filtered rows. I’ve shown how much easier it is to use filtered rows and how much easier it is in the future to maintain.
I’ve also attached the source files from the 2 examples so you may do your own comparisons.
You may download the source files here
Dear David,
In My case I have 1 Combo Box for Year and 1 combo box for Month. I create 1 more hidden combo to do filter rows with the combination. Is it ok or it will degrade the performance.
Considering performance which one is better vlookup or filter rows?.
Please advise.
Thanks & Regards
Anwar
Hi Anwar,
I would still recommend going to filtered rows for the next combo box.
1. Filtered rows is still better than vlookups
2. Filtered rows is easier to maintain. You don’t need a formula for every cell involved in the lookup.
Hope that helps.
Hello David,
i’ve developed some Custom Component to do the same
have a look here
http://www.boprofessionals.com/?q=node/8
Regards
Amr
Hi David,
I am facing one issue in WebI report which need vlookup type functionality.
I am able to implement Vertical lookup in BO report but when I try to implement one further thing which is actually required my logic is getting messed up.
I used merged dimension concept and my report has two queries. One query has new timestamp as prompt and another query has old timestamp as prompt.
There are orders which are placed and has some delivery date. So lets suppose old timestamp is 2005 and we have orders(a,b,c,d) which have delivery date ( 1 feb,1 marc,1 aprl, 1 june) and their count (1,1,1,1)
New timestamp is 2006 and we have orders (a,b,d,e) which have delivery date (1 feb, 1 marc, 1 nov, 1 dec) and their count (1,1,1,1)
Now if we compare above data we can clearly see that order “e” is the new order and order “c” is the cancelled order.
I am able to get this data in the report.
The problem is coming because of order “d” whose delivery date is 1 june in 2005 and in 2006 it has changed to 1 nov.
In my table as soon as I drag delivery date, this record comes as new order for 2006 and cancelled order for 2005
because for 1 June order “d” is not available in 2006 timetsamp so it is considered as cancelled one for 2005 and for 2006, it is available on 1 nov, so it is considered as new order. (I am assuming this happened because now order for 2006 is compared with order for 2005 for each delivery date when delivery date is there in the table)
The order for which delivery date is changed is getting considered as new or cancelled order when delivery date is there in the table as column
My Horizontal table has to have these fields
1st row Delivery date-> Delivery dates
2nd row Oldtimestamp(2005) -> count of order
3rd row newtimestamp(2006) -> count of order
4rt row Difff -> new order count-old order count
5th row New order count -> counts of new order
6th row Cancelled order count -> counts of cancelled order
I hope i am able to state my problem in a clear manner
Please suggest any approach which help me in resolving this issue
I already tried creating detail objects of “order” object but this also not worked properly because the merged delivery date is there in the table.
.
Many Thanks