Back to How-Tos

Create a Detail Table from a Linear Data Structure

Original Author: Rodrigue Noubissie  

Introduction

A data structure is linear when its elements form a sequence where each element has a unique successor and predecessor. Data elements are traversed one after the other and only one element can be directly reached while traversing.

A linear data structure has the property that its elements can be adequately displayed in a straight line.

Example of linear data structures include Arrays, Stacks, Queues and linked lists. ClientID,ClientName,Desc1,Qty1,Amount1,Desc2,Qty2,Amount2,…,Descn,Qtyn,Amountn

In the above screenshot of our sample CSV data file, each client’s purchased items’ description, quantity and total cost (amount) are laid out in a linear fashion with each sequence given an index number from 1 to n. Therefore, we can expect the maximum number or detail records for any one client to be equal to n

This guide demonstrates how the above n items can be extracted and inserted into a detail table in the Connect DataMapper module.

Creating the Detail Table

Step 1: Create arrays for storing elements of the linear data

Add an Action step to store all Description, Quantity and Amount for each record.

var arrDesc = [], arrQty = [], arrAmount = [];

for(var i = 1; i<=6; i++){
  if(data.extract(("Desc" + i), 0)){
    arrDesc.push(data.extract(("Desc" + i), 0));
    arrQty.push(data.extract(("Qty" + i), 0));
    arrAmount.push(data.extract(("Amount" + i), 0));
  }	
}

Step 2: Create a detail table

Add a Repeat step: * Set Repeat type to: Until statement is true * Maximum iterations on each line: arrDesc.length; * Condition Based on JavaScript with: steps.currentLoopCounter; * Operator: is equal to * Right hand operator based on JavaScript with: arrDesc.length;

Step 3: Extract detail elements

Inside the Repeat Step,

  • Insert a Condition step
  • Based on JavaScript with the expression: steps.currentLoopCounter;
  • Operator: is equal to
  • Based on: Value
  • Value: 0
  • In the True branch of the above condition, Add an Extract step and extract the Description, Quantity and Total. For instance, to extract the Description:

  • Set the Mode to JavaScript
  • With the expression: arrDesc[steps.currentLoopCounter-1];
  • In the False branch of the condition:

  • Add a Goto step From Current position and Move by -1 (line)
  • Copy the Extract step from the True branch and paste it in the False branch after the above Goto step.

  • Finally, after the Condition but still within the Repeat loop, make sure your Goto step Moves by 1 (line).

Other Data Types

The principle for other data types is similar. The key is to extract and place the linear data into arrays. For instance let us assume we are given the below fixed length data structure where fields are aligned in columns with each field having a fixed length or width.

1 NAME ITEM1 ITEM2 ITEM3 ITEM4 COLOR1 COLOR2 COLOR3 COLOR4 2 Mike Pen Headset blue red 3 Jane Car Cup Jacket red green blue 4 Roger Shoes white

All that is required to place the items and their corresponding colors in a detail table is to create two arrays:

var arrItems = data.extract(12,41,0,1,"<br />").trim().split(" ");
var arrColors = data.extract(42,70,0,1,"<br />").trim().split(" ");

Although the method by which the arrays are constructed will differ from one data type and structure to another and apart from the array names, the logic applied for creating the detail table is the same as above: