Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
Hi Omar,
I didn't finish reading, but here:
I think you mean it should be associated with lines 1, 2 and 5..
yes
soluion:
add
final:
noconcatenate
load distinct * resident table1;
drop table table1;
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
I need the currency field to make the selections..
with your solution, there is no change on the duplicated lines.. maybe you've missed something ?
If you remove currency when you distinct you get
actually he's looking to get this table but WITH the currency field..
so currency1 as CURRENCY would do it.