Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Matching data from two tables

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

2 Replies
jerem1234
Specialist II
Specialist II

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
Champion III
Champion III

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