Help to eliminate duplication

[OmarBenSalem]

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 5. 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:

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

stalwar1‌, I'm counting on you !

[YoussefBelloum]

Hi Omar,

I didn't finish reading, but here:

I think you mean it should be associated with lines 1, 2 and 5..

[OmarBenSalem]

yes

[OmarBenSalem]

soluion:

add

final:

noconcatenate

load distinct * resident table1;

drop table table1;

[ogster1974]

Hi Omar

Don't you need to remove the CURRENCY field to use distinct to remove the dups?

final:

noconcatenate

load distinct CDR, PRODUCT, currency1,currency2,Notional1,Notional2 resident table1;

drop table table1;

Regards

Andy 

[OmarBenSalem]

I need the currency field to make the selections..

[YoussefBelloum]

with your solution, there is no change on the duplicated lines.. maybe you've missed something ?

[ogster1974]

If you remove currency when you distinct you get

[YoussefBelloum]

actually he's looking to get this table but WITH the currency field..

[ogster1974]

so currency1 as CURRENCY would do it.