topic Re: Compare data of different tables using text object in QlikView

Compare data of different tables using text object

Mon, 28 Apr 2014 19:32:50 GMT

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

Re: Compare data of different tables using text object

MK_QSL — Mon, 28 Apr 2014 19:35:43 GMT

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

Re: Compare data of different tables using text object

swuehl — Mon, 28 Apr 2014 19:37:27 GMT

Try maybe

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

Re: Compare data of different tables using text object

its_anandrjs — Mon, 28 Apr 2014 19:37:58 GMT

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.

Re: Compare data of different tables using text object

Mon, 28 Apr 2014 19:41:18 GMT

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

Re: Compare data of different tables using text object

Mon, 28 Apr 2014 19:46:18 GMT

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

Thank you!

Re: Compare data of different tables using text object

maxgro — Mon, 28 Apr 2014 19:48:59 GMT

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

Re: Compare data of different tables using text object

Mon, 28 Apr 2014 19:53:08 GMT

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

Re: Compare data of different tables using text object

MK_QSL — Mon, 28 Apr 2014 19:57:06 GMT

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

Re: Compare data of different tables using text object

Mon, 28 Apr 2014 19:57:12 GMT

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

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

regards

Darek

Re: Compare data of different tables using text object

maxgro — Mon, 28 Apr 2014 20:03:34 GMT

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

Re: Compare data of different tables using text object

swuehl — Mon, 28 Apr 2014 20:57:05 GMT

=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

];

Re: Compare data of different tables using text object

MK_QSL — Mon, 28 Apr 2014 21:04:52 GMT

Steve is Right...

My Answer is wrong !

Correct is

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

Re: Compare data of different tables using text object

Mon, 28 Apr 2014 21:07:15 GMT

Like mine answer at 3:57 PM

Re: Compare data of different tables using text object

MK_QSL — Mon, 28 Apr 2014 21:14:46 GMT

or this ?

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

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