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 !

[OmarBenSalem]

no.

to be more precise, the first table contains aggregated data; while the second contains all the details.

The user will select a line in the first table:

the line will contain (not all fields are mandatory) CDR, PRODUCT and CURRENCY

when selecting this line, it will transfer to a second table with all the details of the second table associated to the selected line.

So CURRENCY as it is have to stay

[OmarBenSalem]

stalwar1‌; any hint?

[sunny_talwar]

to tell you the truth... I did got through your thread... but had a tough time understanding what you are looking to do.... do you happen to have a sample you can share for us to troubleshoot this in? I know you already shared the inline load statements... but it might help to see the chart you have and I don't want to guess what or how you built your front end objects.

[OmarBenSalem]

I don't..

In fact; in the real application; the first table is an alert table; each day it will contain around 40 alerts (lines);

every Line has a CDR (mandatory field) ,product(optional), CURRENCY(optional)

Each line will also have an aggregated_Amount.

The second table is a details'related table to explain each amount (how was it calculated).

The second table contains around 4 millions lines.

The purpose is: a user select a line; click on the aggregated amount.

On the click, it will transfer him to the second table containing all the details relative to the selected amount(LINE) ; the total number of table 2 has to be equal to the aggregated amount of the first table 1(obviously).

Now, How are the 2 tables associated. (as in the example)

Table1:

contains CDR, PRODUCT, CURRENCY

Table2:

Contains cdr,product,currency1,currency2 and 50 other details level fields..

How it should be done:

0) if a line of Table 1 contains only

CDR:70

we should bring of the  second table ALL the lines containing cdr:70

1) if a line in table 1 contains:

CDR:70

PRODUCT: x

we should bring of the  second table ALL the lines containing cdr:70 and product : x

2) if a line in table 1 contains:

CDR:70

PRODUCT: x

CURRENCY: EUR

we should bring of the  second table ALL the lines containing cdr:70 and product : x

and (currency1=EUR OR currency2=EUR)

3) if a line in table 1 contains:

CDR:70

PRODUCT: TND

we should bring of the  second table ALL the lines containing cdr:70 and (currency1=TND OR currency2=TND)

etc...

Hope that was clearer?