cancel
Showing results for 
Search instead for 
Did you mean: 

Reverse Pivot with Data collision problem!

former_member498903
Participant
0 Kudos

Hi Experts!

I am facing Data collision problem while using reverse pivot transform in my program.

Currently I have more than one row with the same key and value in the source column. And I need to convert the output from row to column level data and due to collision I am not able to convert the same.

with the option "Input data is grouped" and "Duplicate value", "First Row/ Last Row" I can get the 'First row duplicate value' or 'Last row duplicate value' but instead I need to get all the duplicate row data into column level data.

Example

I followed 2-3 suggestions available on internet but end up having data collision problem.

Kindly suggest.

Please help.

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member498903
Participant
0 Kudos

Hello,

Any other update on the reverse pivot with data collision error.

Regards,

Neil

former_member198401
Active Contributor
0 Kudos

Hi Neil,

The data looks inconsistent in the screenshot which you have shown.

In order to convert data from rows to column you need to have one Pivot axis column and multiple axis values.

It seems that the Pivot Axis column contains NULL values and this is the reason why you are getting the data collision error.

Below example should work successfully and the reverse pivot transform should work smoothly.

Non-Pivot Column = Name

Pivot Axis column = Year

Axis Value= 2012_Salary, 2013_Salary, 2014_Salary, 2015_Salary

Pivoted Columns = Salary

former_member498903
Participant
0 Kudos

Hi Arun,

Thanks for your suggestion.

I had configured the pivot axis column and i tried assigning them the axis values but the challenge is that i have single Pivot axis column which is normal but to assign value in axis value i have 'more than 1 row' for with the 'same value'. This line is important in better understanding of a problem.

Generally, in normal case we have 1 pivot axis column and 1 row with 1 unique axis value, which get converted smoothly with the reverse pivot transform.(which you have also described in your example) but in my case i have 1 pivot axis column but multiple rows with 1 unique values. please check the attached screenshot for problem understanding.

so, I configured the values as

1. Pivot axis column - Group1 column value (Group_3) from the screenshot attached

2.  Axis value - Column1 which is rows containg value 3 (multiple times in a row) and it has different values in different columns.

please download the attached copy for your understanding.


if you closely look at the first part which is source, i have converted that using reverse transform, the every subsequent row values are converted from  row to column, but as soon it reaches to 3 where the column value is 3, which repeats the same value, i expect the output shall have the multiple rows for axis value 3 because except axis value 3 all other values are different.


Regards,

Neil



Former Member
0 Kudos

Hi Neil,

You should avoid using reverse pivot in this scenario, rather try splitting data into 2 flows and then using the join merge them.

Regards,

Sandeep

former_member498903
Participant
0 Kudos

Hi Sandeep,

If I do not use reverse pivot then i cannot get the output in row to columner form. And evenif i do not use reverse pivot and a simple query then I need to segregate each group manually with case transform. And there is no restriction on the number groups and possibly there would be more than 300+ groups. which is not feasible.

if you are suggesting some different grouping then please kindly elaborate.

Regards,

Neil

Former Member
0 Kudos

Hi Neil,

I didn't mentioned using case transform. What I meant was to flow your data in 2 Query transform and then merge this using another Query transform with a join and map your to respective output columns using decode function.

For Example:- output.column1 will mapped to decode(input.column1=1,input.column2,Null).

Once loaded you can then handle the nulls.

Regards,

Sandeep

former_member498903
Participant
0 Kudos

Hi Sandeep,

I tried this solution earlier also. After your suggestion also i tried doing it but with the join condition it gives the data collision error.

if you want i can share with you the source file separately on your mail ID.

kindly let me know.

Regards,

Neil_Beginner

Former Member
0 Kudos

Hi Neil,

Avoid using join then and simply try mapping using decode function explained in the image for all columns. The reverse pivot would have also given you the same result. You have to handle the nulls in the end.

Regards,

Sandeep

former_member498903
Participant
0 Kudos

Hi Sandeep,

I appreciate your effort.

I request, can you check the screenshot below, which I am expecting whether that is same you are showcasing in your output.

if you are not able to check the image, please download it as it attached herewith.

I guess the output that you suggested is little differ than the one i attached.

Regards,

Neil

Former Member
0 Kudos

The Output I gave and the output reverse pivot will give, will be same, to reach to your output you have to handle the NULLs.

former_member498903
Participant
0 Kudos

Hi Sandeep,

I will try implementing what you have suggested.

Can you share the program developed ATL file with me, so similarly based on that i will implement the login onto my program.

I checked your profile but won't be able to see email anywhere. kindly update your profile so i can communicate with you over email or else you can share it with me on my below mail address. -

neilrane2010@gmail.com

Regards,

Neil

Former Member
0 Kudos

Hi Neil,

I'll not be able to send any ATL, as I currently don't have a system setup. I have updated my profile for you communicate anything you want.

Regards,

Sandeep

former_member316405
Participant
0 Kudos

Hi Neil,

Can you explain in detail what kind of output you need? It's not clear with above explanation.

Thanks,

Niranjan

former_member498903
Participant
0 Kudos

Hi Niranjan,

Try to download and view the output copy I attached herewith for more understanding of a output.

I am getting data collision problem because in my source there is more than 1 row with the same key or value. hence if there is one unique value in particular column which we want convert from row to column then there is no problem but when there are multiple value with same key then it should also be replicate in the same form instead terminating with the data collision error.

Regards,

Neil..

Former Member
0 Kudos

Hi Neil,

It is possible to share some sample data from your end.

That will help us understand as well as mimic the problem at our end.

Also share the output you expected from sample data.

Thanks,

Swapnil

former_member498903
Participant
0 Kudos

Hi Swapnil,

There is lot communication happend at below threads, I have attached sample size as an example for reference, can you please go through them.

Regards,

Neil