Skip to Content

Remove duplicats in bods4.1

Dear Experts

  I have a requirement that i want to delete duplicates in my template table

Ex:

MATNRLAST_DIRECT_COSTLINE_DISCOUNTPRICELOCATION_ORIGINAL27394.518<Null>94.518AS27341.61<Null>94.518INC27341.61<Null><Null>INC10025473304.688<Null>3304.688AS10025471485.23<Null>3304.688INC10025471485.23<Null>3464.74INC10025473464.74<Null>3304.688PTE100263916.67<Null>16.67AS1002639829.25<Null>16.67INC1002639829.25<Null>1141.45INC10026391141.45<Null>16.67PTE

  Here the MATNR ( Material No) is duplicated many times. How to delete this data based on condition. I have tried in query transform distinct row. Its not working.

I am coming from ABAP background

In ABAP syntax

Delete adjacent duplicates from <TABLE NAME> comparing MATNR LOCATION_ORIGINAL


This statement simply gives correct result as

MATNRLAST_DIRECT_COSTLINE_DISCOUNTPRICELOCATION_ORIGINAL27394.518<Null>94.518AS10025473304.688<Null>3304.688AS100263916.67<Null>16.67AS

How to achieve the same in BODS?

Kindly suggest in 1 or 2 steps.

Regards

Vijay Mukunthan

error.png (9.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Dec 26, 2014 at 10:10 AM

    Hi Vijay,

    It can be seen from the table snapshot that there are no duplicates in your data. Its only the MATNR has different entries and they are unique so Data Services will always repeat MATNR along with the other attributes

    As far as the logic of your ABAP query, you are grouping records by MATNR and LOCATION_ORIGINAL. The same can be done in Data Services by Adding the MATNR and LOCATION_ORIGINAL in Group By  and SUM(PRICE) under the select tab in the query transform.

    The SQL Query generated by Data Services will be as follows:

    select MATNR,LAST_DIRECT_COST, LINE_DISCOUNT, SUM(PRICE), LOCATION_ORIGINAL from TABLE_NAME where LOCATION_ORIGINAL = 'AS'

    group by MATNR, LOCATION_ORIGINAL

    This should work...

    Regards

    Arun Sasi

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 26, 2014 at 10:11 AM

    Distinct rows will return the rows that have a different value in at least one column. Therefore, in your case, it returns all.

    Why don't you use a simple condition in the where-clause of a query transform?

         LOCATION_ORIGINAL = 'AS'

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Arun

        I have addressed the remove duplicate in different way.

      Solution:

      I wrote a SQL script in Post-Load Commands to remove duplicate. It worked fine. The script i used is as follows

      delete from se2_sd_15_sales_price_all_lc

      where rowid not in

      (select min(ROWID)

      from se2_sd_15_sales_price_all_lc

      group by matnr )

      @ Drik

      I am coming from ABAP background so always my mind set tell to do coding to achieve it. Now it time to change my mind set. I saw your design how to achieve that. Now i understood that i should follow your steps. None the less i have 4 more developments. Where i will face lots of issue. I will approach here for more knowledge.

      Thanks and Regards

      Vijay Mukunthan