Compare data of different tables using text object

[#1]

I need to find out the matching distinct values in col1 of table 1 and table2. I need the result in a text object.Also, if possible I do not wish to create any additional tables in the data model. Can anyone help?

TABLE1:
LOAD * INLINE [
COL1, COL2
A,2
B,4
C,5
D,2
H,3
];

TABLE2:
LOAD * INLINE [
COL1,COL4
A,4
B,4
A,23
];

[MK_QSL]

As per your question what result you are looking for this tables?

[swuehl]

Try maybe

=concat({<COL2 = {"*"},COL4={"*"} >}DISTINCT COL1,', ')

[its_anandrjs]

When you load this two tables then there is common field name COL1 and then there is association between two tables and you get a primary key field then how you identify the key values is from which table.

[#5]

Anticipated result = 2 since values 'A' and 'B' are common in both tables

[#6]

I getting the output as 'A,B'. Can I get its count which is '2' instead.

Thank you!

[maxgro]

=concat(aggr(if(count(COL2)+count(COL4)>1,COL1),COL1),', ')

[#8]

Thank you.Can I get a count of this result??

[MK_QSL]

=Index(concat({<COL2 = {"*"},COL4={"*"} >}DISTINCT COL1,', '),',')

[#10]

simple change concat into count in swuehl expression, like this:

=count({<COL2={'*'},COL4={'*'}>}distinct COL1)

regards

Darek