I am currently looking to de-duplicate data in a dataset. Reason being, I need to group together all accounts reporting on a criteria, so we may then sum and total the values of each account to get an accurate idea of how different criteria affects different levels. However, some accounts will report multiple times on the same criteria.
We have a custom object called issues, that we tie cases to, to determine which accounts are affected by x issue.
I am having difficulty getting the grouping to first show grouped values by the accountid, and only to show one report of this accountID.
q = load "Unduplicated_MRR_Issue_Test"; q1 = group q by 'AccountId'; q = foreach q generate first('AccountId') as 'AccountId', 'AccountId.Global_Account_Spend_Formula__c' as 'AccountId.Global_Account_Spend_Formula__c', 'AccountId.Global_Parent_Name__c' as 'AccountId.Global_Parent_Name__c', 'AccountId.Name' as 'AccountId.Name', 'AccountId.intacct_ID__c' as 'AccountId.intacct_ID__c', 'Issue_Number__c' as 'Issue_Number__c'; q2 = foreach q1 generate 'AccountId' as 'AccountId'; q = order q by 'AccountId.Global_Parent_Name__c' asc; q = limit q 100;
So here it shows the account ID ending in AAC reported three times, but I really just need to see this once to then perform a sum. So ideally it would just be 3 rows in the table.
How would I go about grouping the accountIDs to just show the accountIDs once?