13 Replies Latest reply: May 18, 2018 7:18 AM by omar bensalem RSS

    Help to eliminate duplication

    omar bensalem

      Hi all ( stalwar1)

       

      Here what I have:

      table1:

       

       

      load * Inline [

      CDR, PRODUCT,CURRENCY

       

      70630,,

      70630,FX,

      70630,,EUR

      70630,,TND

      70630,FXRATE,

      ];

       

      table2:

      load * Inline [

      cdr,product,currency1,currency2,Notional1,Notional2

      70630,FX,EUR,TND,500,-600                                                             // Line 1

      70630,FXRATE,EUR,,1000,                                                                // Line 2

      70630,FXRATE,TND,GBP,-2000,300                                                    // Line 3

      70630,FX,TND,GBP,-900,200                                                                  // Line 4

      70630,FX,EUR,TND,-200,-400                                                              // Line 5

      ];

       

      What I want to do is join the 2 tables.

      WHY?


      => If for example I select from table1:

       

      CDR : 70630

      PRODUCT: FX

       

      It should be associated to :

      Line 1 and Line 4 => sum(Notional1) = 500-900=-400

       

       

      If I select from table1:

       

      CDR:70630

      CURRENCY: EUR

       

      it should be associated to LINE1,2 and 3. Why? because CURRENCY EUR exists in Currency1 OR Currency2 of each of the 3 lines!

       

      So, with that being said, I think that the join should be based once on currency1, once on currency2.

      Here What I did

       

      table1:

       

       

      load * Inline [

       

      CDR, PRODUCT,CURRENCY

      70630,,

      70630,,

      70630,FX,

      70630,,EUR

      70630,TND

      70630,FXRATE,

      ;

       

       

      table2:

      load * Inline [

      cdr,product,currency1,currency2,Notional1,Notional2

      70630,FX,EUR,TND,500,-600

      70630,FXRATE,EUR,,1000,

      70630,FXRATE,TND,GBP,-2000,300

      70630,FX,TND,GBP,-900,200

      70630,FX,EUR,TND,-200,-400

       

       

      ];

       

      Join(table1)

      load  cdr as CDR,product as PRODUCT, currency1 as CURRENCY, currency1,currency2,Notional1,Notional2 Resident

      table2;

       

      Join(table1)

      load cdr as CDR,product as PRODUCT, currency2 as CURRENCY, currency1,currency2,Notional1,Notional2 Resident

      table2;

       

      drop Table table2;

       

       

      BUT: with this, some lines will be duplicated; because:

      CURRENCY=EUR would be associated to  lines1,2,5

      and CURRENCY=TND would be associated to line 1,3,4,5

       

      So, the line 1, and 5 would be brought 2 times in this case !

       

      Please refer to this image:

      Capture.PNG

       

      What should I do? How can associate the 2 tables without having to deal with duplicates?

       

      stalwar1, I'm counting on you !