topic Count in where clause in QlikView

Count in where clause

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I would like to implement the count function into a where clause in the load statement. The script fails. Am I missing some aggregation?

Data:

LOAD * INLINE [

Company, Sector, CorporateGroup, Year, DataX, DataY, DataZ, DataA

A, Financials, Group1, 2010, 1, 3, 1, 9

A, Financials, Group1, 2011, 9, 9, 1, 2

A, Financials, Group1, 2012, 1, 2, 3, 5

B, Financials, Group2, 2010, 2, 3, 1, 7

B, Financials, Group2, 2011, 2, 77, 1,3

B, Financials, Group2, 2012, 2, 3, 4, 8

C, Automotive, Group1, 2010, 4, 5, 10, 2

C, Automotive, Group1, 2011, 2, 6, 18, 2

C, Automotive, Group1, 2012, 3, 5, 10, 1

] where count(CorporateGroup)>3;

What I exptect is to exclude Company B (Group 2) completely.

Re: Count in where clause

sunny_talwar — Mon, 22 Aug 2016 09:57:21 GMT

I think all the companies here have a count of 3, it seems all the 3 companies will be removed after we fix the code above. Why do you only expect Company B to go away? Is this because they are Group 2?

Re: Count in where clause

Mon, 22 Aug 2016 10:00:11 GMT

Yes right! In the column CorporateGroup I count three times Group 2. Those rows I would like to exclude.

Re: Count in where clause

sunny_talwar — Mon, 22 Aug 2016 10:03:34 GMT

Not 100% sure what the requirement is, but try this:

Data:

LOAD * INLINE [

Company, Sector, CorporateGroup, Year, DataX, DataY, DataZ, DataA

A, Financials, Group1, 2010, 1, 3, 1, 9

A, Financials, Group1, 2011, 9, 9, 1, 2

A, Financials, Group1, 2012, 1, 2, 3, 5

B, Financials, Group2, 2010, 2, 3, 1, 7

B, Financials, Group2, 2011, 2, 77, 1,3

B, Financials, Group2, 2012, 2, 3, 4, 8

C, Automotive, Group1, 2010, 4, 5, 10, 2

C, Automotive, Group1, 2011, 2, 6, 18, 2

C, Automotive, Group1, 2012, 3, 5, 10, 1

];

Right Join (Data)

LOAD Company

Where Flag = 1;

LOAD Company,

If(Sum(If(CorporateGroup = 'Group2', 1, 0)) > 3 or

Sum(If(CorporateGroup = 'Group2', 1, 0)) = 0, 1, 0) as Flag

Resident Data

Group By Company;

Re: Count in where clause

migueldelval — Mon, 22 Aug 2016 10:06:24 GMT

Hi Benchmarking,

Could you try this?

where count(CorporateGroup)>3 and CorporateGroup<>Group2;

Regards

Miguel del Valle

Re: Count in where clause

Mon, 22 Aug 2016 10:08:59 GMT

Ok thanks for the example. Acutally, I am not looking for Group2 specifically. This was just the example. It should be applied to any row where in the column corporate group the record appears more than three times.

Re: Count in where clause

sunny_talwar — Mon, 22 Aug 2016 10:13:17 GMT

What repeats 3 times? Group2 or any Group?

Re: Count in where clause

Mon, 22 Aug 2016 10:19:22 GMT

Group2! Group 1 repeats 6 times.

Data:

LOAD * INLINE [

Company, Sector, CorporateGroup, Year, DataX, DataY, DataZ, DataA

A, Financials, Group1, 2010, 1, 3, 1, 9

A, Financials, Group1, 2011, 9, 9, 1, 2

A, Financials, Group1, 2012, 1, 2, 3, 5

B, Financials, Group2, 2010, 2, 3, 1, 7

B, Financials, Group2, 2011, 2, 77, 1,3

B, Financials, Group2, 2012, 2, 3, 4, 8

C, Automotive, Group1, 2010, 4, 5, 10, 2

C, Automotive, Group1, 2011, 2, 6, 18, 2

C, Automotive, Group1, 2012, 3, 5, 10, 1

D, Automotive, Group3, 2010, 4, 5, 10, 2

D, Automotive, Group3, 2011, 2, 6, 18, 2

D, Automotive, Group3, 2012, 3, 5, 10, 1

];

Further example. Now I would expect Group 2 and Group 3 rows to be excluded.

Re: Count in where clause

sunny_talwar — Mon, 22 Aug 2016 10:22:42 GMT

Got it, try this:

Data:

LOAD * INLINE [

Company, Sector, CorporateGroup, Year, DataX, DataY, DataZ, DataA

A, Financials, Group1, 2010, 1, 3, 1, 9

A, Financials, Group1, 2011, 9, 9, 1, 2

A, Financials, Group1, 2012, 1, 2, 3, 5

B, Financials, Group2, 2010, 2, 3, 1, 7

B, Financials, Group2, 2011, 2, 77, 1,3

B, Financials, Group2, 2012, 2, 3, 4, 8

C, Automotive, Group1, 2010, 4, 5, 10, 2

C, Automotive, Group1, 2011, 2, 6, 18, 2

C, Automotive, Group1, 2012, 3, 5, 10, 1

D, Automotive, Group3, 2010, 4, 5, 10, 2

D, Automotive, Group3, 2011, 2, 6, 18, 2

D, Automotive, Group3, 2012, 3, 5, 10, 1

];

Right Join (Data)

LOAD CorporateGroup

Where Flag = 1;

LOAD CorporateGroup,

If(Count(CorporateGroup) > 3, 1, 0) as Flag

Resident Data

Group By CorporateGroup;

Re: Count in where clause

prashantbaste — Mon, 22 Aug 2016 10:25:36 GMT

Hi benchmarking

Please find attached qvw where your requirement is implemented. Check & Confirm.

Hope this will help you to resolve your issue.

Regards,

Prashant P Baste