Hi,
Can Reverse pivot be optimized .my job is taking too long to process just 1000 records .
shazi58 (BOB member since 2016-02-01)
In case you don’t find any other solution, replace reverse pivot with as many query transform as the number of columns you need. As an example, consider the following table:
SHOP - Sales - Qtr
Shop1 - 100 - Q1
Shop1 - 400 - Q2
Shop1 - 300 - Q3
Shop1 - 500 - Q4
Shop2 - 120 - Q1
Shop2 - 440 - Q2
Shop2 - 350 - Q3
Shop2 - 510 - Q4
divert the source flow 4 times (one for each quarter) and populate the following record structure:
SHOP - Qtr1 - Qtr2 - Qtr3 - Qtr4
with 3 NULLS and 1 value (the one for which your value is defined).
For instance, output for Qtr3 would be:
SHOP - Qtr1 - Qtr2 - Qtr3 - Qtr4
Shop1 - NULL - NULL - 300 - NULL
Shop2 - NULL - NULL - 350 - NULL
Then join the 4 flows always choosing the non NULL values, so to obtain:
SHOP - Qtr1 - Qtr2 - Qtr3 - Qtr4
Shop1 - 100 - 400 - 300 - 500
Shop2 - 120 - 440 - 350 - 510
This should speed dramatically up.
My 2 €cents
CLS69 
(BOB member since 2009-06-11)
Turn on the “Input data is grouped” in the reverse pivot and then make sure the data is actually sorted in the correct order upstream from the reverse pivot.
As was said above, if you can, avoid the reverse pivot altogether. I often get greatly improved performance by pushing down the reverse pivot functionality to the database.
eganjp 
(BOB member since 2007-09-12)