Matching data from two tables

[#1]

Hi All

I have two tables in data model and I want to eliminate unmatched records between two tables.

In below example i want to show only ABC,DCS values only and i want to exclude BCD .

how we can do it in script level,pls provide your suggestions.

table1:

Key,MAT,value

1,     ABC,     1

2,     BCD,    2

3,      DCS   ,3

Table 2 :

Key, Comp,value

1,  ABC, 2

3, DCS,3

output

Key,MAT,value

1,     ABC,     3

3,      DCS   ,6

Thanks

[jerem1234]

Hi SwathiRaj P,

     You will need to do joins in the script. I created an example from your data, please find attached dashboard. I used the script:

Table1:

Load * Inline [

Key,MAT,value

1,ABC,1

2,BCD,2

3,DCS,3

];

Inner Join(Table1)

Table2:

Load Key,Comp as MAT, value2 Inline [

Key, Comp,value2

1,  ABC, 2

3, DCS,3

];

Final:

NoConcatenate Load Key, MAT, value+value2 as Value

Resident Table1;

Drop Table Table1;

Hope this helps!

[its_anandrjs]

Hi,

Another way of the same for doing is

Tmp:
LOAD Key, MAT as Comp, value as Value1;
LOAD * INLINE [
Key, MAT, value
1, ABC, 1
2, BCD, 2
3, DCS, 3
];
Inner Join

LOAD Key, Comp, value;
LOAD * INLINE [
Key, Comp, value
1, ABC, 2
3, DCS, 3
];

LOAD
Key, Comp,
Sum(value)+Sum(Value1) as Comm
Resident Tmp
Group By Key, Comp;
DROP Table Tmp;

Regards

Anand