How to best combine/map values from separate inputs?

I’m trying to produce output containing Value and Item ID from separate files.

I have two input files for this, with different naming for the fields For example,
Transaction Name=TRANSACTION
First Name=FNAME
LastName=LNAME

In addition, one file contains the id, the other contains the value. I need to combine these into an output with Instance (text value) and the ID value. I have a gate setup to create a doc with input0 and input1 as shown below.

Input0
“Instance”:“Transaction Name”
“ID”:“bae77”

Input1
“TRANSACTION”:“AC00014623”

What I want this to look like is:

{TextValue":“AC00014623”
“Item”:{“id”:“bae77”}

I have about 40 of these pairs, slightly different in each file.

Any recommendations or ideas?

What if I were to transform the first input file based on name?

Transform: Instance”:“Transaction Name" to Instance”:“TRANSACTION" so that
Input0
“Instance”:“TRANSACTION”
“ID”:“bae77”

Input1
“TRANSACTION”:“AC00014623”

Still, trying to use input0 as a a mapping in this case.

Try a Join snap with a “1 = 1” join path as shown here:

Followed by a Mapper:

@ptaylor Thanks for the response. This seems to work fine for one row, but not for multiples, especially since I have some unmatched.

Can I ask a few questions on use of a join?

If I want to combine 2 documents (one with values, one with IDs) into one as follows:

Desired Output:
[
{
“TRANSACTION”: “AC00014623”,
“FIRST_NAME”: “Jorge”,
“LAST_NAME”: “HereorThere”,
“TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
“FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
“LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
}
]

or, in the case of multiples, using the same WIDS:
[
{
“TRANSACTION”: “AC00014649”,
“FIRST_NAME”: “Sarah”,
“LAST_NAME”: “Whereever”,
“TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
“FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
“LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
},
{
“TRANSACTION”: “AC00014649”,
“FIRST_NAME”: “Collette”,
“LAST_NAME”: “PorterBeer”,
“TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
“FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
“LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
}
]

I have uploaded my slp file with what I have so far.Sub - WID Joiner Test_2020_08_25.slp (24.4 KB)

I’m really unclear on what your use case is. You said what the Desired Output is, but what’s the input?

Also, please read the documentation for the Join snap if you haven’t yet.

@ptaylor Apologies that I only put the input files in the attachment. Here they are for your reference:
Input file 1
[{“TRANSACTION”: “AC00014623”,
“FIRST_NAME”: “Jorge”,
“LAST_NAME”: “Whoever”}]

Input file 2:
[{“TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
“FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
“LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
}]

The use case at this point is basically an APPEND the WID values to each input1 document. File 2 does not change. File 1 may have many rows.

Note: I was able to accomplish this via hard coding the values to variables in a mapper, but the reference id’s may change from time to time, and it is better practice to pull from a file, or web service call. I hope this helps explain what I’m trying to accomplish.

Sorry, I’m still not following. There are no values in common between the two files, so how would you expect a join to work?

@ptaylor Correct. There are currently no matched fields. In other tools, I have been able to append one file with another.

@acesario - From what you are describing, I think @pataylor provided the correct solution with the Join snap using a 1=1 condition. Assuming that file 1 has an arbitrary number of records, and file 2 has only 1 record, it would successfully append the fields in file 2 for each record in file 1. This would match what you did with the hardcoded values in the Mapper that you describe earlier.

@acesario I think what I’m understanding is that one of your files might have an array with more than one element, but the default parsing turns each element into a separate output document. You can change that by setting “Process array” to false on the JSON Parser. That will produce a single output document whose data is the full array. Before you can feed that into the Join, you’ll have to use a Mapper to map the array to the field of an object. I’ve attached a sample pipeline.

Community 8107_2020_08_27.slp (9.8 KB)

Here are the inputs…

fileA.json:

[
    {
        "file": "A"
    }
]

fileB.json:

[
    {
        "file": "B",
        "id": 1
    },
    {
        "file": "B",
        "id": 2
    }
]

Output of the Join:

[
  {
    "fileA": {
      "file": "A"
    },
    "fileB": [
      {
        "file": "B",
        "id": 1
      },
      {
        "file": "B",
        "id": 2
      }
    ]
  }
]

That combines all of the data from both input files into a single document. You can modify the Mappers to move things to the right places.

1 Like

@ptaylor Thank you for the sample pipelien and for the info on “Process array”… that is very helpful.
I very much appreciate your patience and persistence as I learn this.

1 Like