Note: This release is in preview. Features described here don’t become generally available until the latest general availability date that Salesforce announces for this release. Before then, and where features are noted as beta, pilot, or developer preview, we can’t guarantee general availability within any particular time frame or at all. Make your purchase decisions only on the basis of generally available products and features.

Format the Results of a Query with Multiple Datasets

When a query has multiple datasets, the query results are formatted using the XMD of the first loaded dataset.

For example, the Quota dataset is loaded first so its XMD is used to format the projected columns Amount and Percent Attained.

1-- The Quota dataset's XMD is used because it is loaded first
2quota = load "Quota";
3ops = load "Opportunity";
4q = cogroup ops by 'Owner.Name', quota by 'Employee';
5q = foreach q generate quota.'Employee' as 'Account Owner', sum(ops.'Amount') as 'Amount', trunc(sum(ops.'Amount')/sum(quota.'Quota')*100,2) as 'Percent Attained';

However, the Quota dataset’s XMD doesn't contain the Amount field, so the projected amount is formatted as a number rather than as currency.

The Opportunity dataset’s XMD contains an Amount field that is formatted as currency. To format the amount as currency, load the Opportunity dataset first.