Trouble with Joining

CloverDX Platform
1

I have an excel spreadsheet with the following data
[sheet 1] companies → properties like (permalink, name, year_founded, …etc)

[sheet 2] investments ->properties like (company_permalink, investor_permalink, name, …etc)

Note:
- a company can have multiple investors
- an investor can invest in multiple companies

I’m trying to create what feels like a materialized view
with the following output structure (as a json file) … where all the investor permalinks get rolled into a json array as shown below
{ companies: [
{
name : “ABC COMPANY”,
year_founded : 2004,
company_investors: [ “investor_permalink_1”,“investor_permalink_2”, “investor_permalink_3” …]
}
]
}

The problem is with the ExtHashJoin. I’ve attached the source graph file. Please

2

Hi takejerax,

You forgot to attach the graph. And please try to describe your issue in more details. I am not sure about where the issue lies. It seems like your post ends in the middle of the sentence.

Kind regards,

3

Thanks for the response. Let me try to post again.

Here is the original microsoft excel file i’m using as input. => (XLSDATA_READER)
https://www.dropbox.com/s/ip3azvap5hpwz … _2013.xlsx
It has 7 “sheets” . I’m interested in only the following 2 sheets

  1. Companies
  2. Investments

Companies sheet has a list of just - companies : with the primary key field being “permalink”
Investments sheet has a list of investments in these companies : it has a key : “investor_permalink” and a foreign key → “company_permalink”

Each of the companies above can have many investors. Similarly each investor can invest in many companies.
(essentially a one->many relationship for either “key”)

What i’m trying to do is generate a json output with the following structure:
https://www.dropbox.com/s/3ffpw8xtry9ov … anies.json

I have tried to do this in a bunch of ways and it produces the incorrect output.
Following are 2 methods i used to try and do this.
Method 1 : https://www.dropbox.com/s/6ntjwml1odc2c3a/Test.grf

Method 2: https://www.dropbox.com/s/vjm1snf21hnps … ments2.grf

After the ExtHashJoin
for each company (key:permalink, foreignkey:company_permalink)
i need to fold in all investor_permalinks into a json array called “company_investors” as shown in the link to companies.json above.

We would be really grateful for your help. I’ve tried searching through google and the forums extensively and haven’t been able to get an answer. Thanks in advance.

4

I have prepared an example graph for you. You should get the main idea from it and then you should be able to modify it according to your needs.
JSONWriter.grf
It is not necessary to use any of the join components. Joining can be done directly in JSONWriter, using binding and key + parentKey properties.

If you had any questions, please let me know.

Kind regards,