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;

This isn't the whole table, but should demonstrate the idea: This isn't the whole table, but should demonstrate the idea:

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?


I did not get what you are trying to do with that query where are q1 and q2 used but normally

q = load "Blade1_v2";
result = group q by ('AccountId', 'AccountId.Name');
result = foreach result generate q.'AccountId' as 'AccountId', 
q.'AccountId.Name' as 'AccountId.Name', unique(q.'AccountId') as 'A';
result = order result by  'AccountId.Name' asc;
result = limit result 2000;

would work

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.