Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arsenal1983
Creator
Creator

Count null

Hello,

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

TAB_1:

KEYCOL_1COL_2COL3
1745519400
2633609169
3483325808
4721107514
5827649429
6148489653

 

TAB_2:

KEYCL_1CL_2CL_3CL_4
1890212310812
218992480472
34055883217
599851917449
6808319493830
789191835783

 

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

A small glitch has to be fixed. Try:

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

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

View solution in original post

6 Replies
Taoufiq_Zarra

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_0-1592831537648.png

 

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
arsenal1983
Creator
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.

tresesco
MVP
MVP

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)

 

arsenal1983
Creator
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:
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:

KeyKey1Val1Key2Val2
1a1a101a20
2a2a102a20
3a3a103a20
9a9a10  
4a  4a20
5a  5a20

 

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

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

Have You got any idea how to fix it?

tresesco
MVP
MVP

A small glitch has to be fixed. Try:

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

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

arsenal1983
Creator
Creator
Author

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