Spot on Paul, the licence files had been applied but I think one of the services on your link hadn't been restarted. Thanks v much ... View more

Hi PAzevedo Thank you very much for your reply. You understood me perfectly and your code does exactly what I want! I have certainly added hash object lookup to my list of "things to use in the future". It runs very quickly indeed. For my personal benefit could you explain this part please? if quote&i & new_quote>quote&i then    new_rank+1; How does the second ampersand work in this statement? Thank you very much for your suggestion, and once again to everyone else who took time to help me. Jamie ... View more

Thanks very much Linlin, this is great. One more question if you have the time to spare? Is there a way of having only 1 new column shown in the output rather than them being parsed individually? I.E. output which would look like this: CUSTOMERID     QUOTENO     PRODUCTID     QUOTE     RANK     NEW_RANK 5000                    1                    17                    53.24          1          1 5000                    1                    18                    55.27          2          1 5000                    1                    19                    60.67          3          1 5000                    2                    17                    44.96          1          1 5000                    2                    19                    45.17          2          1 5000                    2                    20                    49.27          3          1 6050                    1                    17                    72.65          1          1 6050                    1                    19                    77.45          2          1 This looks strange I know, however when the percentage is reduced from the example 50% to say 5% and the rest of the PRODUCTIDs are added (of which there are around 60) then it provides the exact requirement for our row-level OLAP cube. Thanks again for your help (everyone), much appreciated ... View more

Hi Linlin, Your suggestion is close - what your code does is show the new rank based on the most expensive productID reducing their price. What im looking to do is report on each ProductID individually. So if ProductID 17 reduced their price by .50 then their new rank would (still) be 1. If productID 18 reduced their price by .50 then their new rank would be 1. If ProductID 19 reduced their price by .50 the new rank would be 1. Its difficult because each reduced price needs to be compared to the original prices as its a view for each company separately. If you can spare the time to make any further suggestion it would be greatly appreciated. Sorry if im not explaining this very well. Thanks again Jamie ... View more

Thanks for your replies. Art, there propbably are better ways of doing what im doing but it's fairly complex and I know some people frown upon people posting without having tried themselves. Ill try explain what im doing - feel free to ignore though as it could take more time than you have to give: We have a comparison website where a customer submits a request for a quotation and we return multiple quotes per customer to choose from. Each customer can have multiple quotations and multiple prices returned per quote. Example of table: CUSTOMERID      QUOTENO      PRODUCTID      QUOTE      RANK 5000                    1                    17                      53.24          1 5000                    1                    18                      55.27          2   5000                    1                    19                      60.67          3 5000                    2                    17                      44.96          1 5000                    2                    19                      45.17          2   5000                    2                    20                      49.27          3   6050                    1                    17                      72.65          1   6050                    1                    19                      77.45          2 So from above, Customer 5000 submitted 2 quotation requests. ProductID 17 was the cheapest (rank 1), product 18 was second cheapest and so on. Customer 6050 also submitted a quote and 2 quotations were given. We are trying to do some "what if" analysis. So if ProductID 19 reduced their price by 5% how would it affect the RANK of their price. A new variable in the table containing the new RANK based on 5% reduction would be enough for us to build an OLAP cube off the raw table. If you can suggest any concepts on how to do this then it would be greatly appreciated. If not dont worry. Thanks again Jamie ... View more

Hi Art Thank you for your reply. Ill explain what im trying to do. I have a table with a list of unique ProductIds. I want to pass each ProductID to a macro and run the macro. I was attempting to create an array of the productIDs and pass the array to the macro. is this possible or would I have to pass the ProductIds over 1 at a time? Thanks again ... View more