topic Re: How to get count of values from second table based on values present in 1st table , without using joins ? in QlikView

How to get count of values from second table based on values present in 1st table , without using joins ?

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

Hi All ,

How to get count of values from second table based on values present in 1st table , without using joins ?

which means both table are not linked (associated) to each other in qlik terms .

Example : In table 1 i have field : Visitor , which has values A , B ,C etc . Similarly i have a field called Emp in second table .

So i wanted to show count of all the entries of A , B and C in Emp field of second table

I have attached sample data with output .

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

sunny_talwar — Thu, 15 Mar 2018 09:03:15 GMT

Check attached

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

chinnuchinni — Thu, 15 Mar 2018 09:52:39 GMT

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

chinnuchinni — Thu, 15 Mar 2018 09:57:09 GMT

NULLASVALUE '-' ;

Companyvisit:

LOAD Company,

Visitor,

Date

FROM (ooxml, embedded labels, table is Sheet2);

empvist:

LOAD Emp,

Store,

Date

FROM (ooxml, embedded labels, table is Sheet3);

abc:

load Visitor as Name,num(Count(Visitor))as cmpnyvisit Resident Companyvisit Group by Visitor ;

join(abc)

xyz:

load Emp as Name,num(Count(Emp))as storevisit Resident empvist Group by Emp ;

a123:

load Name,(cmpnyvisit+storevisit) as total Resident abc ;

DROP Tables Companyvisit,empvist ;

EXIT SCRIPT ;

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

shiveshsingh — Thu, 15 Mar 2018 10:41:04 GMT

May be this?

You can make a dummy field and then match it with both the tables(name),

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

shekhar_analyti — Mon, 19 Mar 2018 09:10:41 GMT

Hi Sunny Bhai ,

Actually i needed only data from Visitor field of Company Visitor table not Emp data ,

what i see in your attachment is , that data from Visitor and Emp both are concatenated .

Also both table should not be related , but currently it is related by date field which should not be the case

Thanks & Regards

Shekar

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

shekhar_analyti — Mon, 19 Mar 2018 09:12:35 GMT

Hi Praveen ,

As mentioned earlier , join is not the requirement .

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

sunny_talwar — Mon, 19 Mar 2018 11:20:59 GMT

I am not sure I follow.... do you want to expand on this a little.

Re: How to get count of values from second table based on values present in 1st table , without using joins ?

shekhar_analyti — Mon, 19 Mar 2018 11:32:54 GMT

Sorry Bhai , for not being articulate . However answer to below will help

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

Thanks & Regards

Shekar