Announcements
cancel
Showing results for
Did you mean:
Creator

## Count null

Hello,

In my data model I have two tables linked by key column.

TAB_1:

 KEY COL_1 COL_2 COL3 1 745 519 400 2 633 609 169 3 483 325 808 4 721 107 514 5 827 649 429 6 148 489 653

TAB_2:

 KEY CL_1 CL_2 CL_3 CL_4 1 890 212 310 812 2 189 924 80 472 3 405 588 32 17 5 998 51 917 449 6 808 319 493 830 7 891 91 835 783

How can I count how many records (keys) I have in TAB_1 and don't have in TAB_2?

1 Solution

Accepted Solutions
MVP

A small glitch has to be fixed. Try:

=Count({<Key2-={"=Key2=Key1"}>} Key)

=Count({<Key1-={"=Key1=Key2"}>} Key)

6 Replies

on solution

``````TAB_1:
KEY, COL_1, COL_2, COL3
1, 745, 519, 400
2, 633, 609, 169
3, 483, 325, 808
4, 721, 107, 514
5, 827, 649, 429
6, 148, 489, 653
];

TAB_2:
LOAD if(not Exists(KEY),1,0) as count,* INLINE [

KEY, CL_1, CL_2, CL_3, CL_4
1, 890, 212, 310, 812
2, 189, 924, 80, 472
3, 405, 588, 32, 17
5, 998, 51, 917, 449
6, 808, 319, 493, 830
7, 891, 91, 835, 783
];``````

and in interface->  how many records (keys) I have in TAB_1 and don't have in TAB_)

``=sum(count)``

output :

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Creator
Author

Thanks a lot but would like to add this count in the app rather and I'm looking for something more universal.

This solution will not work if I will try to count records that are in the second table and are not in the first one.

MVP

Try creating an additional copy of key in each table like:

``````t1:
Key as Key1 Inline [
Key, Val1
1, 10
2, 10
3, 10
9, 10];

t2:
Key as Key2 Inline [
Key, Val2
1, 20
2, 20
3, 20
4, 20
5, 20]``````

The in the UI:

For no. of keys not there in t2 but in t1:

=Count({<Key2-={"=Key1"}>} Key)

For no. of keys not there in t1 but in t2:

=Count({<Key1-={"=Key2"}>} Key)

Creator
Author

Hi Tresesco,

Your solution works good but only if Key is a number. In my case Key is a string and then the formula counts wrong (the other thing is why):

t1:
Key as Key1 Inline [
Key, Val1
1a, 10
2a, 10
3a, 10
9a, 10];

t2:
Key as Key2 Inline [
Key, Val2
1a, 20
2a, 20
3a, 20
4a, 20
5a, 20];

in App we have table:

 Key Key1 Val1 Key2 Val2 1a 1a 10 1a 20 2a 2a 10 2a 20 3a 3a 10 3a 20 9a 9a 10 4a 4a 20 5a 5a 20

=Count({<Key2-={"=Key1"}>} Key) = 5

=Count({<Key1-={"=Key2"}>} Key) = 4

Have You got any idea how to fix it?

MVP

A small glitch has to be fixed. Try:

=Count({<Key2-={"=Key2=Key1"}>} Key)

=Count({<Key1-={"=Key1=Key2"}>} Key)

Creator
Author

I don't know how but it works. Truly you are the Qlik MVP 🙂 thanks a lot!

Community Browser