How do I capture the values and write to one row from duplicate customer ids, example:
100 1 AB1
100 2 AB3
100 3 AB2
100 2 AB4
I should delete DUPLICATE customerID, and I should get one row as follows:
CUSTOMERID AB1 AB2 AB3 AB4
100 1AB1 2AB3 3AB2 2AB4
How do I capture the values and write from duplicate rows
Moderators: chulett, rschirm, roy
sorry it should be like this
How do I capture the values and write to one row from duplicate customer ids, example:
100 , 1, AB1
100 , 2, AB3
100 , 3, AB2
100 , 2, AB4
I should delete DUPLICATE customerID, and I should get one row as follows:
CUSTOMERID, AB1, AB2, AB3, AB4
100 , 1AB1, 2AB3, 3AB2, 2AB4
How do I capture the values and write to one row from duplicate customer ids, example:
100 , 1, AB1
100 , 2, AB3
100 , 3, AB2
100 , 2, AB4
I should delete DUPLICATE customerID, and I should get one row as follows:
CUSTOMERID, AB1, AB2, AB3, AB4
100 , 1AB1, 2AB3, 3AB2, 2AB4
If you search vertical pivoting in the forum, you should able to find something.
solution:
use row process function in the transformer, which compares the previous with present row. If present value same as previous row value(customer id) then populate you input filed into their corresponding target fields and rest of the columns with zeros. Finally use an aggregator grouping on customer id and max function rest of fields.
Best solution from performance point view, write into work table in the database and write pivot query.
Vijay
[quote="ashok"]sorry it should be like this
How do I capture the values and write to one row from duplicate customer ids, example:
100 , 1, AB1
100 , 2, AB3
100 , 3, AB2
100 , 2, AB4
I should delete DUPLICATE customerID, and I should get one row as follows:
CUSTOMERID, AB1, AB2, AB3, AB4
100 , 1AB1, 2AB3, 3AB2, 2AB4[/quote]
solution:
use row process function in the transformer, which compares the previous with present row. If present value same as previous row value(customer id) then populate you input filed into their corresponding target fields and rest of the columns with zeros. Finally use an aggregator grouping on customer id and max function rest of fields.
Best solution from performance point view, write into work table in the database and write pivot query.
Vijay
[quote="ashok"]sorry it should be like this
How do I capture the values and write to one row from duplicate customer ids, example:
100 , 1, AB1
100 , 2, AB3
100 , 3, AB2
100 , 2, AB4
I should delete DUPLICATE customerID, and I should get one row as follows:
CUSTOMERID, AB1, AB2, AB3, AB4
100 , 1AB1, 2AB3, 3AB2, 2AB4[/quote]
Hi Ashok,
There was a similar discussion on this topic last week. Check if this link helps
viewtopic.php?t=89216
Rgds
--Rich
Pride comes before a fall
Humility comes before honour
There was a similar discussion on this topic last week. Check if this link helps
viewtopic.php?t=89216
Rgds
--Rich
Pride comes before a fall
Humility comes before honour