- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A small glitch has to be fixed. Try:
=Count({<Key2-={"=Key2=Key1"}>} Key)
=Count({<Key1-={"=Key1=Key2"}>} Key)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
on solution
TAB_1:
LOAD * INLINE [
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 :
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try creating an additional copy of key in each table like:
t1:
Load *,
Key as Key1 Inline [
Key, Val1
1, 10
2, 10
3, 10
9, 10];
t2:
Load *,
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
Load *,
Key as Key1 Inline [
Key, Val1
1a, 10
2a, 10
3a, 10
9a, 10];
t2:
Load *,
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A small glitch has to be fixed. Try:
=Count({<Key2-={"=Key2=Key1"}>} Key)
=Count({<Key1-={"=Key1=Key2"}>} Key)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I don't know how but it works. Truly you are the Qlik MVP 🙂 thanks a lot!