Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?
Yes right! In the column CorporateGroup I count three times Group 2. Those rows I would like to exclude.
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;
Hi Benchmarking,
Could you try this?
where count(CorporateGroup)>3 and CorporateGroup<>Group2;
Regards
Miguel del Valle
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.
What repeats 3 times? Group2 or any Group?
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.
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;
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