The flowgraph offers advanced nodes for the following use cases:
You have learned how to split a data set into different records by using a Case node. But, how do you flatten a dataset across multiple columns, based on its content? For example, if a column contains the country value, let's put each country into its own column. As you will see, we can do that with a Pivot node.
You've learned how to fetch an attribute such as a phone number and add it to a sales record with the corresponding person. But if this person has multiple phone numbers a join would create a copy of the transaction data record with this person for each phone number. Suppose, you need only the first phone number, a Lookup node is what you need. With a combination of a Lookup node and a Pivot node you could capture all the phone numbers.
When a record changes in a source system, you usually want to update the data target with the change. You can choose to overwrite the existing record with the newer version, but you could also decide to keep the existing record, mark it as 'old', and load the new record alongside. This way you keep the old records as well as provide the new record to the business. Use a History Preservation node to accomplish this. This special node allows us to automatically add time-validity information to the old and new records.
The following table lists all flowgraph nodes in the Advanced section
Advanced Node Types
| Node Type | USe Case |
|---|---|
History Preservation | Allows for maintaining older versions of rows when a change occurs by generating new rows in a target. |
Lookup | Retrieves a column value or values from a Lookup table that match a lookup condition you define. |
Map Operation | Sorts input data and maps output data. |
Pivot | Creates a row of data from existing rows. |
Unpivot | Creates a new row for each value in a column identified as a pivot column. |
Table Comparison | Compares two tables and produces the difference between them as a dataset with rows flagged as INSERT, UPDATE, or DELETE. |
Let's take a closer look at two important examples: The Lookup node and the Pivot node.
Suppose, you need an address or phone number of a person and you have an address book with all addresses and numbers. You want the most frequent address, or the one that fits best with the sales date. Then you should use the lookup node.
What is a lookup? A lookup is almost a join. You define matching criteria and add the corresponding values from another column, but it has some differences:
- The lookup table should not change dynamically during data load of the main records. Therefore, the lookup transformation can be processed in real-time.
- You can specify lookup table column and sort value pairs to invoke a sort, which selects a single lookup table row when multiple rows are returned.
- Configure default values in the form of constants to be output when no Lookup table rows are returned.
The lookup only returns one result per People_Id.
But if you want two results you use a combination of a join and a pivot node, provided that the contact counter always starts with 1. On the left side of the following table, we have the result of a join. The right side shows the result of the pivot node with the desired outcome.
How does it work? Let's take a look at the pivot node principle in another example with measures.
This example is created by the following user interface:
Let's get familiar with this procedure step by step.
