No Results
Example: Joining Two Datasets
Article Contents

This page provides a working example of joining two data sets, orders and customers, on the customer ID using a JavaScript Transform.

We are going to use the following data:

customers.csv
id|STRING,firstName|STRING,lastName|STRING,birthday|DATE|millis,city|STRING,state|STRING,country|STRING
5e64ad59,Faye,Yundt,976424400000,Lake Aliya,Wyoming,US
9ee3d682,Evans,Stiedemann,537080400000,Garretland,Oklahoma,US
6d9f5ec8,Wilbert,Marks,471070800000,Goodwinberg,Hawaii,US
ba25fe44,Celia,King,667976400000,Cassinton,Nebraska,US
2439f553,Alfreda,Gislason,338702400000,Calistatown,Washington,US
6146eafb,Lucius,Kihn,345186000000,Kesslerview,Wisconsin,US
cfef4e7b,Laurianne,Medhurst,108878400000,New Casandramouth,Nebraska,US
9d2f7dbb,Mitchell,Pfeffer,63608400000,Lake Ernesto,Alabama,US
f9952dcc,Hobart,Stroman,840686400000,Nicolabury,Kansas,US
75ec3044,Derek,Ankunding,520920000000,West Timmothyside,West Virginia,US
orders.csv
id|STRING,customerId|STRING,product|STRING,price|NUMBER,orderDate|DATE|millis
b2e47adf,9d2f7dbb,Sleek Metal Ball,838.00,1506632578728
1462c8f8,cfef4e7b,Ergonomic Plastic Gloves,326.00,1518911898479
b8c05771,2439f553,Fantastic Wooden Gloves,779.00,1500521442115
e1f644b4,2439f553,Tasty Concrete Car,606.00,1508251308148
0fb1f137,f9952dcc,Awesome Rubber Tuna,212.00,1502966390931
c574ecf5,75ec3044,Incredible Granite Towels,852.00,1495241960160
f1af8a58,5e64ad59,Licensed Wooden Cheese,680.00,1503309096975
7a5b7dcd,75ec3044,Refined Rubber Salad,78.00,1514356449491
8c9c4c12,2439f553,Licensed Soft Chips,730.00,1500890788519
0cd8bb39,ba25fe44,Gorgeous Granite Pizza,276.00,1493928317451
8282f191,f9952dcc,Gorgeous Wooden Keyboard,168.00,1492341014802
c71f71e4,9ee3d682,Intelligent Metal Shoes,379.00,1492824880569
2a63c992,6146eafb,Fantastic Soft Shoes,479.00,1518763672701
5398cb13,6146eafb,Sleek Cotton Towels,119.00,1514919664548
e63d695f,9d2f7dbb,Intelligent Soft Bacon,981.00,1506545287484
30197394,2439f553,Practical Rubber Table,971.00,1515592746193
416b46ec,ba25fe44,Awesome Cotton Soap,848.00,1491203491848
389a80e3,6d9f5ec8,Intelligent Soft Hat,626.00,1517564546571
3be460ee,6146eafb,Rustic Fresh Car,9.00,1510491003393
6fa09415,75ec3044,Refined Plastic Shoes,257.00,1511316390623
8d44d5d4,5e64ad59,Tasty Steel Pants,68.00,1491390308036
e06c0c23,9ee3d682,Awesome Metal Soap,171.00,1498305742330
c272529a,9ee3d682,Intelligent Wooden Gloves,66.00,1499725455759
4037f6e9,f9952dcc,Intelligent Wooden Cheese,328.00,1509215844332
451a9fe1,6d9f5ec8,Handcrafted Plastic Chips,856.00,1516908411790
bf74a547,9d2f7dbb,Intelligent Wooden Tuna,246.00,1493843203515
7b69d7cd,9ee3d682,Generic Concrete Table,19.00,1515958165173
4308833e,cfef4e7b,Generic Plastic Shoes,217.00,1502807688544
f45e1fb6,5e64ad59,Tasty Fresh Pizza,734.00,1508822736830
06bc76ae,75ec3044,Rustic Frozen Chips,889.00,1495505987725
16730358,9d2f7dbb,Refined Plastic Keyboard,297.00,1518136684983
08ef453e,5e64ad59,Handmade Concrete Bike,199.00,1505087479382
a5d96b7b,f9952dcc,Handcrafted Metal Mouse,679.00,1515120248865
d0033a8e,ba25fe44,Tasty Soft Hat,820.00,1502545004126
efacd323,ba25fe44,Licensed Granite Tuna,845.00,1504548690439
b06bb329,2439f553,Rustic Frozen Chicken,666.00,1509990109909
3c4bd111,f9952dcc,Small Fresh Chair,786.00,1511087651241
80e79073,5e64ad59,Incredible Wooden Shirt,77.00,1515819286891
e669da5f,2439f553,Generic Rubber Mouse,173.00,1509655520854
bf46e78c,2439f553,Awesome Concrete Hat,760.00,1492319140954
4127aa4d,75ec3044,Practical Wooden Chair,531.00,1512004446779
37214063,6d9f5ec8,Small Soft Fish,769.00,1505541192320
7a82919d,6d9f5ec8,Intelligent Wooden Sausages,682.00,1504909957451
47f99cce,6d9f5ec8,Ergonomic Metal Chair,828.00,1498945483175
ac6d0c5b,cfef4e7b,Incredible Fresh Keyboard,25.00,1490744585472
b165ff19,ba25fe44,Incredible Soft Gloves,534.00,1494440840412
dc49d493,2439f553,Refined Metal Mouse,317.00,1512284946867
d3b21278,75ec3044,Ergonomic Frozen Mouse,759.00,1501700944566
be37e8ec,5e64ad59,Gorgeous Rubber Cheese,940.00,1494486962866
275c0f55,2439f553,Gorgeous Concrete Hat,816.00,1489049211695

JavaScript Transform

Instructions:

  1. Download orders.csv and customers.csv.
  2. Create a Server Filesystem Connection.
  3. Create two CSV Feeds off of the previously created connection (one for Customers and one for Orders).
    a) For both of these, in File Name upload the respective CSV files (customers.csv and orders.csv) and select them from the dropdown.
  4. Create a JavaScript Transform off of either of the CSV Feeds, configured as follows:
    a) Provide a Transform Name (for example, Customer_Orders).
    b) In Script References > Referenced Datasets, make sure Orders and Customers are selected.
    c) In Script, paste the following:

function getAttributes(sourceAttrs, nodeVars, secVars, sourceRecords) {
    var orderAttrs = sourceAttrs['Orders']; // alternatively, each attribute list can be reference via numerical index with
                                            // respect to their position in the referenced dataset list
    var customerAttrs = sourceAttrs['Customers'];
    var attributes = [];
    orderAttrs.forEach(function(attr){
        if (attr.name == 'id') {
            // alias order.id to order.orderId
            attr.name = 'orderId';
        }
        attributes.push(attr);
    });
    customerAttrs.forEach(function(attr) {
        if (attr.name !== 'id') {
            // orders already has a customerId field, so we can skip adding the id field from the customers data set
            attributes.push(attr);
        }
    });
    return jsAttributesSuccess(attributes);
}
 
function getRecords(sourceRecords, nodeVars, secVars, attributes) {
    var orders = sourceRecords['Orders'];
    var customers = sourceRecords['Customers'];
    var records = [];
    orders.forEach(function(order) {
        // set the orderId value since we aliased the id attribute
        order.orderId = order.id;
        customers.forEach(function(c) {
            if (order.customerId == c.id) {
              for (var prop in c) {
                  if (prop.search(/^_/) == -1 && prop !== 'id') {
                    // records and attributes both have internal Java representations, _asJavaObject
                    // records also have _clone
                    // these can be ignored by end users, but they are enumerable; prop.search() is used in this case to filter them out
                    // additionally, for this example, we don't need c.id since the order already has a customerId field
                    order[prop] = c[prop];
                  }
              }
              records.push(order);
            }
        });
    });
    records.sort(function compare(a,b) {
        // date values are java.util.Date instances, so we can use the Date API to make comparisons
        // alternatively, we could have compared the values of a.orderDate.getTime() and b.orderDate.getTime() (timestamps in milliseconds)
        return a.orderDate.compareTo(b.orderDate);
    });
    return jsRecordsSuccess(records);
}

    d) Go to Data Preview where you should see the end result — two datasets being joined.
        

Terms | Privacy
×