Discussion board where members can get started with Qlik Sense.
Hi all ( stalwar1)
Here what I have:
load * Inline [
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.
=> If for example I select from table1:
CDR : 70630
It should be associated to :
Line 1 and Line 4 => sum(Notional1) = 500-900=-400
If I select from table1:
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
load cdr as CDR,product as PRODUCT, currency1 as CURRENCY, currency1,currency2,Notional1,Notional2 Resident
load cdr as CDR,product as PRODUCT, currency2 as CURRENCY, currency1,currency2,Notional1,Notional2 Resident
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 !
I didn't finish reading, but here:
I think you mean it should be associated with lines 1, 2 and 5..
load distinct * resident table1;
drop table table1;
Don't you need to remove the CURRENCY field to use distinct to remove the dups?
load distinct CDR, PRODUCT, currency1,currency2,Notional1,Notional2 resident table1;
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.