system
August 25, 2006, 9:30am
1
We have a DI 6.51 job with a Reverse Pivot. It has worked fine until yesturday. Now we get the following error:
XRN-130112: Reverse_Pivot The pivot transform is being aborted, due to data collision
Cant find anything on how to solve it. Any clue?
Thank you
Jan
Jan (BOB member since 2005-10-14)
system
August 25, 2006, 9:49am
2
For one key, a pivot axis value occures twice, e.g.
1996 USD 1000
1996 EUR 1000
1996 GBP 1000
should be reverse pivoted into
1996 1000USD 1000EUR 1000GBP
But for some reason, you have another record “1996 EUR 2000” which collides with the other EUR row for 1996.
Werner Daehn (BOB member since 2004-12-17)
system
August 25, 2006, 11:53am
3
My table looks something like this
COLUMN VALUE
A 1
B 2
C 3
D 4
VALUE is set as pivot column and COLUMN as Pivot axis column. A number of Unique axis values are alos entered.
I really cant understand what causes the error?
Jan
Jan (BOB member since 2005-10-14)
system
August 25, 2006, 3:47pm
4
on your source table execute a
select formid, template_id, column_name, source, orderno, …, count()
from sourcetable
group by formid, template_id, column_name, source, orderno, …
having count( ) > 1
listing all columns including the column_name column. All columns except the VALUE column.
Above query should not return any rows, if it does, this is the duplicate row why the transform “aborts” as specified in “duplicate value” property.
Werner Daehn (BOB member since 2004-12-17)
system
June 13, 2007, 2:18pm
5
I’m running into this issue as well…my data looks like this:
StoreID - Title - Name
1 - Manager - John
1 - Manager - Tim
In our situation, it is perfectly fine for a store to have two managers. Is there any way to transform this into:
StoreID - Manager1 - Manager2
1 - John - Tim
traider (BOB member since 2007-03-01)
system
June 13, 2007, 3:07pm
6
Add a counter for each group
StoreID - Title - Name - Counter
1 - Manager - John - 1
1 - Manager - Tim - 2
1 - Clerk - Werner - 1
and use that as Axis Value for the reverse pivot
Werner Daehn (BOB member since 2004-12-17)
system
June 13, 2007, 3:51pm
7
Thats a great idea but now I have another problem…
How would you create such a counter in DI? I have no way to make this change in the source table so I will have to handle it here.
Thanks again for your help.
traider (BOB member since 2007-03-01)
system
June 13, 2007, 10:37pm
8
In 11.7 there is the function gen_row_num_per_group()
Werner Daehn (BOB member since 2004-12-17)
system
June 13, 2007, 10:41pm
9
Perfect!
Thank you
traider (BOB member since 2007-03-01)
system
August 22, 2009, 4:00am
10
Hi,
The Link you posted doesn’t open. could you please suggest me how to add a counter for group using gen_row_num_by_group function
Regards,
Satya
satya.kamarajugadda (BOB member since 2009-03-01)
system
August 23, 2009, 11:15am
11
This chapter uses the gen_row_num_per_group() function
https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Access+to+previous+row+values
Werner Daehn (BOB member since 2004-12-17)
system
August 24, 2009, 7:29am
12
HI I have created a counter for all code columns before the reverse pivot. but still Data Collision Occurs. any help or thoughts would be appreciated
cheers,
Hari.kamarajugadda (BOB member since 2009-08-22)
system
August 24, 2009, 12:31pm
13
So that means that at least one combination of
GL_Account_Code
GL_Account_Name
Department_Dimension_code
…
…
…
(and all other columns listed in the none-pivot columns)
has two rows with the same Month_Short_Name.
Like in
Company_Name GL_Account_Code Month_Short_Name Actual
Office1 B4 Jan 100
Office2 B4 Jan 900
What should be the value in the Jan_Actual column? 100 or 900? The Reverse_Pivot you configured to Abort in such a case.
Werner Daehn (BOB member since 2004-12-17)
system
August 27, 2009, 12:47am
14
Thanks, for the feedback you have given me.Actually, we have multiple sources and one of the source table for a particular ID has got no rows and I have to manually insert some rows from fact table to this ID to populate those in development environment.
Issue’s with my environment.
For Reverse Pivot issue, I have used Aggregate Function, unchecking Input Data Grouped. It worked Perfectly.
Thanks,
Hari.kamarajugadda (BOB member since 2009-08-22)