Forum Discussion

aditya_gupta41's avatar
aditya_gupta41
Contributor
4 years ago
Solved

Handling Multiple 'null values

I am currently having this requirement where i need to handle all the null values coming in input. The common thing i noticed that all these values end with ‘_DATE’ but this is coming in an object. f...
  • ptaylor's avatar
    ptaylor
    5 years ago

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

  • ptaylor's avatar
    ptaylor
    Employee
    5 years ago
    Copy LinkReport

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

    • acesario's avatar
      acesario
      Contributor II
      5 years ago
      Copy LinkReport

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

  • acesario's avatar
    acesario
    Contributor II
    5 years ago
    Copy LinkReport

    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's avatar
    ptaylor
    Employee
    5 years ago
    Copy LinkReport

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

    Followed by a Mapper:

  • acesario's avatar
    acesario
    Contributor II
    5 years ago
    Copy LinkReport

    @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's avatar
      ptaylor
      Employee
      5 years ago
      Copy LinkReport

      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.

  • acesario's avatar
    acesario
    Contributor II
    5 years ago
    Copy LinkReport

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

    • ptaylor's avatar
      ptaylor
      Employee
      5 years ago
      Copy LinkReport

      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?

  • acesario's avatar
    acesario
    Contributor II
    5 years ago
    Copy LinkReport

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

  • koryknick's avatar
    koryknick
    Employee
    5 years ago
    Copy LinkReport

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