topic Count null in QlikView

Count null

arsenal1983 — Mon, 22 Jun 2020 12:49:47 GMT

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?

Re: Count null

Taoufiq_Zarra — Mon, 22 Jun 2020 13:13:22 GMT

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 :

Re: Count null

arsenal1983 — Mon, 22 Jun 2020 14:01:01 GMT

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.

Re: Count null

tresB — Mon, 22 Jun 2020 14:26:02 GMT

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)

Re: Count null

arsenal1983 — Tue, 23 Jun 2020 09:19:08 GMT

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?

Re: Count null

tresB — Tue, 23 Jun 2020 10:09:35 GMT

A small glitch has to be fixed. Try:

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

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

Re: Count null

arsenal1983 — Tue, 23 Jun 2020 10:17:03 GMT

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