Restructure node

The Restructure node can be used to generate multiple fields based on the values of a nominal or flag field. The newly generated fields can contain values from another field or numeric flags (0 and 1). The functionality of this node is similar to that of the Set to Flag node. However, it offers more flexibility. It allows you to create fields of any type (including numeric flags), using the values from another field. You can then perform aggregation or other manipulations with other nodes downstream. (The Set to Flag node lets you aggregate fields in one step, which may be convenient if you are creating flag fields.)

For example, the following dataset contains a nominal field, Account, with the values Savings and Draft. The opening balance and current balance are recorded for each account, and some customers have multiple accounts of each type. Let's say you want to know whether each customer has a particular account type, and if so, how much money is in each account type. You use the Restructure node to generate a field for each of the Account values, and you select Current_Balance as the value. Each new field is populated with the current balance for the given record.

Table 1. Sample data before restructuring
CustID Account Open_Bal Current_Bal
12701 Draft 1000 1005.32
12702 Savings 100 144.51
12703 Savings 300 321.20
12703 Savings 150 204.51
12703 Draft 1200 586.32
Table 2. Sample data after restructuring
CustID Account Open_Bal Current_Bal
Account_Draft_
Current_Bal
Account_Savings
_Current_Bal
12701 Draft 1000 1005.32 1005.32 $null$
12702 Savings 100 144.51 $null$ 144.51
12703 Savings 300 321.20 $null$ 321.20
12703 Savings 150 204.51 $null$ 204.51
12703 Draft 1200 586.32 586.32 $null$

Using the Restructure node with the Aggregate node

In many cases, you may want to pair the Restructure node with an Aggregate node. In the previous example, one customer (with the ID 12703) has three accounts. You can use an Aggregate node to calculate the total balance for each account type. The key field is CustID, and the aggregate fields are the new restructured fields, Account_Draft_Current_Bal and Account_Savings_Current_Bal. The following table shows the results.

Table 3. Sample data after restructuring and aggregation
CustID Record_Count
Account_Draft_Current_
Bal_Sum
Account_Savings_Current_
Bal_Sum
12701 1 1005.32 $null$
12702 1 $null$ 144.51
12703 3 586.32 525.71