About Merging

The two primary ways of merging two different datasets is via Joins and Unions. This guide will not get into the details of how this works, as we’ll assume you’re already familiar. In case you need a brief refresher:

• Joins allow you to “move” one or more columns from one frame to another.
• Unions allow you to “add” one or more records from one frame to another.

Note

Joins and Unions are not supported between data sources which are not in the same locality. e.g. mixing Local data and remote data, or multiple locations of remote data.

This will be available in the future.

Joins

There are multiple ways to join data in Coco Alemana. The easiest way is via the interface. However, you’re also able to perform joins via custom SQL.

Dragging Columns

The easiest way to add one or more columns to a dataset via a Join is by dragging one or more columns from one frame to another. This will set up the join for you, and allows you to specify the join type you’re after.

The resulting join sheet allows you to specify the join key columns, as well as the join type. You can add multiple keys, as well as change the join type according to your needs.

This method only includes the selected columns from the “Source” frame, and not every column.

You can also drag columns even when frames are in full-screen mode. Being in the canvas is not required.

Canvas Based

This method involves physically dragging one frame next to the other, on the vertical axis. This will set up a join for you, with the main difference being that all columns from both frames will be available in the resulting frame. This is most similar to a standard SQL join.

If you don’t want to drag the frames physically, you can also select two frames, and right click to Join. The keyboard shortcut ⌘ J also works.

In both the Dragging Columns case and Canvas Based case, you will see an interface like this…

SQL

The most verbose, but also flexible way of joining is via SQL. Here, you can reference any frame within your project to perform the same types of joins in the interface, but via custom SQL code.

We find that most joins are better done via the interface. If you need to do a more sophisticated join operation, it’s still available as SQL.

For most cases, it’s faster to use the drag columns interface to perform joins.

Unions

Like Joins, there are multiple ways to perform unions in Coco Alemana. We have the interface way, and the custom SQL way.

Unions done in the interface use the UNION ALL strategy. This means that duplicate records between the two frames are kept. If this is problematic, you may want to “Drop Duplicates” afterwards.

Canvas Based

Canvas-based Unions are the preferred way. They handle most of the complications that Unions have.

They will automatically resolve column mappings, even if naming, order, or types are not aligned. This can save several minutes per union you perform.

If you don’t want to drag frames manually, you can also select two frames, and right click to Union. The keyboard shortcut ⌘ U also works.

After you drag the frames together, you will see a view which prompts you to modify the column alignment.

This interface automatically resolves the following:

1. Naming differences
2. Ordering differences
3. Type differences & aligns types accordingly

As long as your columns are somewhat similar, the system should pick it up and align it for you. You’re also able to drop columns which have no match. The mapping is entirely up to you, so you’re able to change it before unioning.

SQL

You can provide a standard SQL UNION clause, albeit requiring much more alignment. This can be okay if your columns are the same name, and in the same order.