topic Re: Lookup , if found then how many times it's found in non related table . Please Help ! in QlikView

Lookup , if found then how many times it's found in non related table . Please Help !

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

My requirement is that there are two tables completely unrelated , it should not be linked , joined as well .

And then values from Field Manager of table A has to been looked up in Field People in Table B , if found then its count .

Table A

Manager	Area	DateA
A	1	2/1/2016
B	2	3/1/2016
C	2	5/1/2016
D	1	6/1/2015
E	3	7/1/2014
F	4	8/1/2018
G	4	9/1/2017
H	5	10/1/2016

Table B

People	Date
A	2/1/2017
A	1/1/2018
B	3/1/2017
B	3/1/2018
C	3/1/2017
F	4/1/2017
F	4/1/2018
G	4/1/2017
G	4/1/2018
k	4/1/2018
L	3/1/2017

Output1
Manager	Times Present in People Column
A	2
B	2
C	1
D	0
E	0
F	2
G	2
H	0

Output2
People	Date	Area
A	2/1/2017	1
A	1/1/2018	1
B	3/1/2017	2
B	3/1/2018	2
C	3/1/2017	2
F	4/1/2017	4
F	4/1/2018	4
G	4/1/2017	4
G	4/1/2018	4

Re: Lookup , if found then how many times it's found in non related table . Please Help !

sunny_talwar — Mon, 19 Mar 2018 11:42:00 GMT

Try this for Output 1

=Count(DISTINCT If(Manager = People, Date))

What exactly are you doing for Output 2? Counting the number of Areas?

Re: Lookup , if found then how many times it's found in non related table . Please Help !

sunny_talwar — Mon, 19 Mar 2018 12:24:55 GMT

Here is the second output

Re: Lookup , if found then how many times it's found in non related table . Please Help !

shekhar_analyti — Mon, 19 Mar 2018 12:43:58 GMT

Thank you Bhai for the reply . When i applied this logic - > Count(DISTINCT If(Manager = People, Date))

to the real data which is having high volume of data , its making the application slow . Is there any other workaround ?
something like lookup in load script , etc ..

Re: Lookup , if found then how many times it's found in non related table . Please Help !

sunny_talwar — Mon, 19 Mar 2018 12:49:53 GMT

Nope!! If you will do calculations with two unconnected tables... you are going to suffer with regards to performance... That is why an association is important. Right now.... you are doing a Cartesian Product of Manager and People to determine the count....