Navigation:  Intermediate Phase > Joining data from multiple sources and Aggregating >

Cheating : Load your graph from here

To load the graph quickly and easily, copy and paste the following XML into "Source" mode of your Graph. Be sure to delete the old XML first. You will find the Source tabs at the bottom of the graph pane. Switch back to graph mode and it will be ready to use. Make sure you have your source data files loaded too. This was covered into the Beginner Phase chapter under "Creating a Project" and "Loading Data".

 


<?xml version="1.0" encoding="UTF-8"?>

<Graph author="fmay" created="Mon Sep 14 10:06:48 BST 2009" guiVersion="2.2.2" id="1252920133068" licenseType="Evaluation license." modified="Wed Sep 16 15:47:01 BST 2009" modifiedBy="fmay" name="LoadCustomers" revision="1.55">

<Global>

<Metadata id="Metadata0" previewAttachment="${DATAIN_DIR}/customers.csv" previewAttachmentCharset="ISO-8859-1">

<Record fieldDelimiter="|" name="I_Customers" previewAttachment="${DATAIN_DIR}/customers.csv" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">

<Field name="CustomerID" type="string"/>

<Field name="CompanyName" type="string"/>

<Field name="ContactName" type="string"/>

<Field name="ContactTitle" type="string"/>

<Field name="Address" type="string"/>

<Field name="City" type="string"/>

<Field name="Region" type="string"/>

<Field name="PostalCode" type="string"/>

<Field name="Country" type="string"/>

<Field name="Phone" type="string"/>

<Field name="Fax" type="string"/>

</Record>

</Metadata>

<Metadata id="Metadata2" previewAttachment="${DATAIN_DIR}/orderdetails.csv" previewAttachmentCharset="ISO-8859-1">

<Record fieldDelimiter="|" name="I_OrderDetails" previewAttachment="${DATAIN_DIR}/orderdetails.csv" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\n" type="delimited">

<Field name="OrderID" type="integer"/>

<Field name="ProductID" type="integer"/>

<Field name="UnitPrice" type="number"/>

<Field name="Quantity" type="integer"/>

<Field name="Discount" type="number"/>

</Record>

</Metadata>

<Metadata id="Metadata1" previewAttachment="${DATAIN_DIR}/orders.csv" previewAttachmentCharset="ISO-8859-1">

<Record fieldDelimiter=";" name="I_Orders" previewAttachment="${DATAIN_DIR}/orders.csv" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\n" type="delimited">

<Field name="OrderID" type="integer"/>

<Field name="CustomerID" type="string"/>

<Field name="OrderDate" type="string"/>

<Field name="OrderValue" type="number"/>

</Record>

</Metadata>

<Metadata id="Metadata3" previewAttachmentCharset="ISO-8859-1">

<Record fieldDelimiter="|" name="O_OrderSummary" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">

<Field name="Company" type="string"/>

<Field name="TotalSales" type="number"/>

<Field name="AverageSaleValue" type="number"/>

<Field name="UnitSales" type="number"/>

<Field name="TempValue" type="number"/>

</Record>

</Metadata>

<Metadata id="Metadata4" previewAttachment="${DATAIN_DIR}/orderdetails.csv" previewAttachmentCharset="ISO-8859-1">

<Record fieldDelimiter="|" name="x_OrderDetails" previewAttachment="${DATAIN_DIR}/orderdetails.csv" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\n" type="delimited">

<Field name="OrderID" type="integer"/>

<Field name="ProductID" type="integer"/>

<Field name="UnitPrice" type="number"/>

<Field name="Quantity" type="integer"/>

<Field name="Discount" type="number"/>

<Field name="CID" type="string"/>

</Record>

</Metadata>

<Property fileURL="workspace.prm" id="GraphParameter0"/>

</Global>

<Phase number="0">

<Node aggregateKey="Company" enabled="enabled" guiHeight="0" guiName="Aggregate" guiWidth="0" guiX="484" guiY="232" id="AGGREGATE0" mapping="$Company:=$Company;$TotalSales:=sum($TempValue);$AverageSaleValue:=avg($TempValue);$UnitSales:=count();" sorted="false" type="AGGREGATE"/>

<Node enabled="enabled" fileURL="${DATAIN_DIR}/customers.csv" guiHeight="0" guiName="Customers" guiWidth="0" guiX="100" guiY="224" id="DATA_READER0" skipRows="1" type="DATA_READER"/>

<Node enabled="enabled" fileURL="${DATAIN_DIR}/orders.csv" guiHeight="0" guiName="Orders" guiWidth="0" guiX="6" guiY="9" id="DATA_READER1" skipRows="1" type="DATA_READER"/>

<Node dataPolicy="strict" enabled="enabled" fileURL="${DATAIN_DIR}/orderdetails.csv" guiHeight="0" guiName="Order Items" guiWidth="0" guiX="7" guiY="104" id="DATA_READER2" skipRows="1" type="DATA_READER"/>

<Node enabled="enabled" guiHeight="0" guiName="Add Customer ID" guiWidth="0" guiX="197" guiY="58" id="EXT_HASH_JOIN0" joinKey="$OrderID=$OrderID;#" joinType="inner" slaveDuplicates="true" type="EXT_HASH_JOIN">

<attr name="transform"><![CDATA[//#TL

 

// Transforms input record into output record.

function transform() {

 $0.CID := $0.CustomerID;

 $0.UnitPrice := $1.UnitPrice;

 $0.Quantity := $1.Quantity;

}

 

// Called during component initialization.

// function init() {}

 

// Called after the component finishes.

// function finished() {}

]]></attr>

</Node>

<Node enabled="enabled" guiHeight="0" guiName="Load Customer Name" guiWidth="0" guiX="396" guiY="102" id="EXT_HASH_JOIN1" joinKey="$CID=$CustomerID;#" joinType="inner" slaveDuplicates="true" type="EXT_HASH_JOIN">

<attr name="transform"><![CDATA[//#TL

 

// Transforms input record into output record.

function transform() {

 $0.Company := $1.CompanyName;

 $0.TempValue := $0.UnitPrice*$0.Quantity;

}

 

// Called during component initialization.

// function init() {}

 

// Called after the component finishes.

// function finished() {}

]]></attr>

</Node>

<Node enabled="enabled" guiHeight="0" guiName="Trash" guiWidth="0" guiX="660" guiY="280" id="TRASH0" type="TRASH"/>

<Edge debugMode="true" fromNode="AGGREGATE0:0" guiBendpoints="" id="Edge1" inPort="Port 0 (in)" metadata="Metadata3" outPort="Port 0 (out)" toNode="TRASH0:0"/>

<Edge debugMode="true" fromNode="DATA_READER0:0" guiBendpoints="" id="Edge7" inPort="Port 1 (slave)" metadata="Metadata0" outPort="Port 0 (output)" toNode="EXT_HASH_JOIN1:1"/>

<Edge debugMode="true" fromNode="DATA_READER1:0" guiBendpoints="" id="Edge8" inPort="Port 0 (driver)" metadata="Metadata1" outPort="Port 0 (output)" toNode="EXT_HASH_JOIN0:0"/>

<Edge debugMode="true" fromNode="DATA_READER2:0" guiBendpoints="" id="Edge9" inPort="Port 1 (slave)" metadata="Metadata2" outPort="Port 0 (output)" toNode="EXT_HASH_JOIN0:1"/>

<Edge debugMode="true" fromNode="EXT_HASH_JOIN0:0" guiBendpoints="" id="Edge10" inPort="Port 0 (driver)" metadata="Metadata4" outPort="Port 0 (out)" toNode="EXT_HASH_JOIN1:0"/>

<Edge debugMode="true" fromNode="EXT_HASH_JOIN1:0" guiBendpoints="" id="Edge0" inPort="Port 0 (in)" metadata="Metadata3" outPort="Port 0 (out)" toNode="AGGREGATE0:0"/>

</Phase>

</Graph>

 

 

The Graph

This is the graph we are going to build in this example.

 

JoinAggreateSample