Prepare Your Combined Data in a Recipe

1. Download the ExchangeRates.csv file to your desktop from here.
2. In Data Manager, click the Data Assets tab.
3. Click New Dataset.
4. Click Upload Files.
5. Navigate to the ExchangeRates.csv file that you downloaded in step 1, select it, and then click Open.
6. Click Next.
7. Click Next.
8. Click Next. (Phew.)
9. Click Close.
10. Confirm that the file upload was successful on the Jobs Monitor. If needed, refresh the view to see the latest upload job status.

1. Click the Recipes tab.
2. Click the North America Sales recipe to open it in data prep.
3. Hover over Append 0 and click .
4. Click Join.
5. Select the ExchangeRates dataset and click Next.
6. Click the preselected values under Join Keys and update to:
   • Append 0: BillingCountry
   • ExchangeRates: Country
7. Click Add.
8. Click the Columns tab and uncheck Country. Although it's needed for the lookup, you don't need to include it in your final dataset. We already have the other BillingCountry column with this same information.
9. Click the Preview tab, and then scroll to the right to confirm that the Rate column is included.
10. Click Apply. The node Join 4 is added to the recipe.
11. Click Save.

Now that you have exchange rate data, use a formula to convert Canadian dollar values to US dollars. To add a Formula transformation, you must first add a Transform node.

1. Click + next to the Join 4 node and select Transform.
2. In the Preview tab, click the Amount column.
3. In the Transform toolbar, select and select Custom Formula.
4. Change the Transformation Name to Convert Currency.
5. To convert to US dollars, enter the following formula."Amount"/"ExchangeRa.Rate"
6. In the Output Type field, select Numeric.
7. Verify that the precision is 10.
8. Set Scale to 2 so the column will use 2 decimal places.
9. In the Show Results In field, notice that the results will be written to a new column. Also, the original column will be kept with the original values.
10. In the Column Label field, change the label of the new column to USD Amount.
11. In the API Name field, change the value to USD_Amount.
12. Click Apply to add the Formula transformation as a step in the Transform node.

Clean the Stage Data

Your recipe has data from different sources and this can lead to inconsistencies and dirty data. For example, you know that opportunity stages in the Canada org are slightly different than stages in the US org. (You know because you changed them right after you signed up for the Canada org.) Use the Replace transformation to fix the inconsistencies in the StageName column.

1. In the Preview tab, click the StageName column, and then click in the Transform toolbar to open the Replace transformation.
2. Change the Transformation Name to Value Proposition Cleanup.
3. Enter ValueProposition in the Find field and Value Proposition in the Replace field.
4. Looking at the Show Results In field, notice the new value will overwrite the existing values in the original column.
5. Click Apply.
6. Repeat steps 1-5 to replace Negotiation & Review with Negotiation/Review in the StageName column. Use the transformation name Negotiation/Review Cleanup.
7. Click Save.

So you’ve cleaned up the stage data. Great! Next, we're going to clean up some columns. For example, you no longer need the Amount and Rate columns that you used to convert amounts to US dollars in the new USD Amount column. Let’s tidy those up next.

Drop Columns and Change Labels

Your recipe has columns that you don’t need. For example, it’s unlikely that any one would ever want to analyze account IDs. And many columns that you might want to keep have unfriendly or unclear names. How do your users choose from the three Name columns?

Use the Columns tab for an uncluttered place to drop columns and change labels.

1. Click the Columns tab. Let's drop some columns from the recipe so they don't appear in the final dataset.
2. Select the AccountId column and click [alt text: Drop Columns].
3. In the Select Columns to Drop field, add these columns also.
   • OwnerId
   • Id (with the API Name CAAccount.Id)
   • Id (with the API Name User.Id)
   • Amount
   • Rate
   • Symbol
   • Tip: Instead of manually entering multiple columns in the Drop Columns transformation, you can select multiple columns on the Columns tab using Cmd+click for MacOS or Ctrl+click for Windows, and then apply the Drop Columns transformation.
4. Click Apply. The columns are dropped from the recipe. Now let's clean up some column labels.
5. In the Columns tab, select the Name entry with the API name Name.
6. Click  to edit attributes, change the label to Account Name, and click Apply.
7. Repeat steps 5 and 6 to change these column labels as well.
   • BillingCountry to Account Country
   • Name (with API Name User.Name) to Owner
   • BillingCity to Account City
   • BillingState to Account State/Province
   • CloseDate to Close Date
   • Type (with API Name CAAccount.Type) to Account Type
   • LeadSource to Lead Source
   • Title (with API Name User.Title) to Owner Title
   • CreatedDate to Created Date
   • StageName to Stage
8. Click Save twice to save our latest recipe changes.
9. Click the Preview tab.

Ahh, that’s better. Your data preparation tasks are done. All that’s left to do is specify where to write the results and then run the recipe to write the results to the specified target.

Create the Dataset and Explore

It's time to add an Output node, which indicates where to write the recipe results. You want to write the results to a dataset. After you specify the dataset details, run the recipe to create and view the final dataset.

1. To view the recipe graph, click .
2. Click + next to the Transform node and select Output.
3. In the Dataset Display Label field, enter North America Sales.
4. In the Dataset API Name field, enter North_America_Sales.
5. Although you can change the app that stores the dataset, leave the default app My Private App in the App Location field.
6. Click Apply.
7. Click Save and Run to save and run the recipe.
8. To see the results, click Back to Data Manager.
9. In the Jobs Monitor tab, check that the status of the North America Sales_recipe job shows Successful. If the status shows as Queued or Running, click to refresh the view until the recipe job finishes.
10. After the recipe completes successfully, click the Data Assets tab.
11. Click on the North America Sales dataset.
12. Click Explore.
13. Under Bars, click the Add a Group button (+) and select Account Country.
14. Under Bars, click the Add a Group button (+) and select Stage. Opportunities from both countries now have the same stage names.
15. Under Bar Length, click Count of Rows, click Sum, and then click USD Amount. All values are now in US dollars.