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
];

[maxgro]

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

[swuehl]

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

Sorry, this is not a correct answer, since it returns 2 just because the separator ',' is positioned after the single character 'A'. As soon as one uses longer field values or more than 2 common key values, the answer is not correct.

Use

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

instead.

Compare the results with a test data model like:

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

C,7

];

[MK_QSL]

Steve is Right...

My Answer is wrong !

Correct is

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

[#5]

Like mine answer at 3:57 PM

[MK_QSL]

or this ?

Still your's and Steve's answer is best...

=SUBSTRINGCOUNT(CONCAT({<COL2 = {'*'},COL4 = {'*'}>}DISTINCT COL1,','),',')+1