Reformatting and reorganizing

Overview

The projects in the repository folder Samples\Basic\Reformatting and reorganizing demonstrate techniques for:

• Converting a flat table to a hierarchy

• Joining tables from separate databases without SQL

• Aggregating child data into the parent record

• Using Table To Stream to manipulate all field values at once

• Converting data from long to wide

Converting a flat table to a hierarchy

The sample project flat to hierarchy shows how to convert from a "flat" table to parent/child tables. The input flat table contains fields for three entities:

• Customers

• Orders

• Order detail

The fields for the Customer and Order tables are duplicated in the input table. This example will:

• Generate unique IDs for Customers, because none are given

• Normalize the structure of the tables into a three-level hierarchy

• Combine the duplicates values

Joining tables from separate databases

The sample project SQL equivalent1 demonstrates how to use the Join tool to perform joins between two tables without using SQL. Since Data Management has a built-in Join tool, you can use it to perform joins between tables in situations where joins are not well-supported by an RDBMS:

• Joining tables from heterogeneous sources (tables of unrelated databases, different files, an RDBMS and a file).

• Processing the complete "outer" join results as well as the inner join.

• Performing non-Cartesian joins.

• Performing joins that would overtax a burdened RDBMS server, such as joins on non-indexed fields.

Many of the other examples use Join in different ways. We suggest that you browse the sample projects to see these various uses.

Aggregating child data into the parent record

The sample project publishers with fem authors demonstrates a special case of the more general problem of aggregating values from child records into parent records. Examples of similar problems are:

• In a database of companies with ownership as child records, assigning fields indicating whether companies are female-owned or minority-owned.

• In a database of salespeople with sales as child records, appending a field indicating the percent of sales from the salesperson's top five customers.

This example finds publishers that work primarily with female authors. It does this by counting the number of female authors for each publisher and comparing it to the total number of authors for each publisher.

Table to stream and back

The sample project Table to stream and back demonstrates how to use the Table To Stream tool to perform operations on all or a subset of the field values at once, preserving the original order and meta-data.

Converting tables to data streams

The sample project table_to_stream_and_back demonstrates using the Table To Stream tool to perform operations on all or a subset of an input file's field values while preserving the original order and meta-data.

Converting data from long to wide

The sample project tall to wide using patterns shows how to use the pattern tools to perform a tall-to-wide conversion, by first creating a normalized group/sequence/attribute/value table, and then converting it to a completely wide table using the Pattern Assembler tool.