Count in where clause

[#1]

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.

[sunny_talwar]

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?

[#3]

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

[sunny_talwar]

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;

[migueldelval]

Hi Benchmarking,

Could you try this?

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

Regards

Miguel del Valle

[#6]

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.

[sunny_talwar]

What repeats 3 times? Group2 or any Group?

[#8]

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.

[sunny_talwar · Accepted solution]

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;

[prashantbaste]

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