SnapLogic - Integration Nation

acesario · ‎08-19-2020

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?

ptaylor · ‎08-27-2020

@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.

View solution in original post

acesario · ‎08-20-2020

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.

ptaylor · ‎08-20-2020

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

Followed by a Mapper:

acesario · ‎08-25-2020

@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)

ptaylor · ‎08-26-2020

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.

SnapLogic - Integration Nation

How to best combine/map values from separate inputs?