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. for all the null values, i need to pass value ‘0000-00-00’. The challenge which i am facing is this is dynamic. sometimes there are overall 28 fields ending with ‘_DATE’ and sometimes its more than 100.

[
  {
  "DEPRECIATIONAREAS": [
      {
        "ASSET": "00005000xxxx",
        "DEP_UNITS": 0,
        "O_START_DATE": "2006-09-01",
        "S_START_DATE": null,
        "INTEREST_START_DATE": null,
        "READINESS": "2006-08-31",
        "INDEX": "",
        "AGE_INDEX": "",
        "VAR_DEP_PORTION": 0.0000,
        "SCRAPVALUE": 0.0000,
		}
		]
	"POSTINGINFORMATION": [
      {
        "ASSET": "000050009142",
        "SUBNUMBER": "0000",
        "CAP_DATE": "2006-08-31",
        "DEACT_DATE": null,
        "INITIAL_ACQ": "2006-08-31",
        "INITIAL_ACQ_YR": 2006,
        "INITIAL_ACQ_PRD": 11,
        "PLRET_DATE": null,
        "PO_DATE": null,
        "CAP_KEY": ""
		}
		]
		}
		]

Can someone provide me a solution for this?

Thanks in Advance

  • aditya_gupta41's avatar
    aditya_gupta41
    4 years ago

    This works. Thanks for your help @j.angelevski

14 Replies

  • j_angelevski's avatar
    j_angelevski
    Contributor III
    4 years ago

    Hi @aditya.gupta41,

    Try with the following expression:

    {}.extend(...$.entries().map(val => {[val[0]]: val[1].map(v => {}.extend(...v.entries().map(date => date[0].contains("_DATE") && date[1] == null ? {[date[0]]: "0000-00-00"} : {[date[0]]: date[1]} )))}))

    This is assuming you have one or multiple arrays in the root object, assuming the arrays have one or multiple objects. This will iterate over each array find the “_DATE” key/value pair, and if a “_DATE” happens to be null, it will change the original value with the “0000-00-00”, and put back everything into the original format.

    • aditya_gupta41's avatar
      aditya_gupta41
      Contributor
      4 years ago

      This didn’t worked for me

      Failure: Map type does not have a method named: map, found in: …]: date[1]} ))). Perhaps you meant: mapKeys, mapValues, Resolution: Please check expression syntax and data types.

      • j_angelevski's avatar
        j_angelevski
        Contributor III
        4 years ago

        It didn’t because the first and second data source that you posted are completely different.
        I updated my expression ( the only issue is that with the previous source, the data was on the root level, but on the new source, the data is within the “data” object ), please refer to the expression below:

        {}.extend(...$data.entries().map(val => {[val[0]]: val[1].map(v => {}.extend(...v.entries().map(date => date[0].contains("_DATE") && date[1] == null ? {[date[0]]: "0000-00-00"} : {[date[0]]: date[1]} )))}))

        Here is the result:

        [
   {
      "data":{
         "TIMEDEPENDENTDATA":[
            {
               "ASSET":"000050",
               "SUBNUMBER":"0000",
               "FROM_DATE":"1900-01-01",
               "TO_DATE":"9999-12-31",
               "W_DATE":"0000-00-00",
               "X_DATE":"0000-00-00"
            }
         ],
         "SELECTIONCRITERIA":[
            {
               "PARAMETER":"GENERALDATA",
               "FIELD":"ASSET",
               "SIGN":"I",
               "OPTION":"E",
               "LOW":"00005000",
               "HIGH":""
            }
         ],
         "REALESTATE":[
            
         ],
         "POSTINGINFORMATION":[
            {
               "ASSET":"0000502",
               "SUBNUMBER":"0000",
               "C_DATE":"2006-08-31",
               "DEACT_DATE":"0000-00-00",
               "PLRET_DATE":"0000-00-00",
               "PO_DATE":"0000-00-00",
               "CAP_KEY":""
            }
         ],
         "NETWORTHVAL":[
            
         ],
         "INVENTORY":[
            {
               "ASSET":"000050",
               "SUBNUMBER":"0000",
               "V_DATE":"0000-00-00",
               "W_DATE":"0000-00-00"
            }
         ],
         "INSURANCE":[
            
         ]
      }
   }
]
  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    4 years ago

    Hey @aditya.gupta41 ,

    The JSON you provided is not valid. Are these two separate samples just posted as one? (DEPRECIATIONAREAS,POSTINGINFORMATION)

    • aditya_gupta41's avatar
      aditya_gupta41
      Contributor
      4 years ago

      These are arrays coming inside the data. I cannot post the actual output, but here is another example:

      [
  {
    "data": {
      "TIMEDEPENDENTDATA": [
        {
          "ASSET": "000050",
          "SUBNUMBER": "0000",
          "FROM_DATE": "1900-01-01",
          "TO_DATE": "9999-12-31",
          "W_DATE": null,
          "X_DATE": null
        }
      ],
      "SELECTIONCRITERIA": [
        {
          "PARAMETER": "GENERALDATA",
          "FIELD": "ASSET",
          "SIGN": "I",
          "OPTION": "E",
          "LOW": "00005000",
          "HIGH": ""
        }
      ],
      "REALESTATE": [

      ],
      "POSTINGINFORMATION": [
        {
          "ASSET": "0000502",
          "SUBNUMBER": "0000",
          "C_DATE": "2006-08-31",
          "DEACT_DATE": null,
          "PLRET_DATE": null,
          "PO_DATE": null,
          "CAP_KEY": ""
        }
      ],
        }
      ],
      "NETWORTHVAL": [

      ],
      "INVENTORY": [
        {
          "ASSET": "000050",
          "SUBNUMBER": "0000",
          "V_DATE": null,
          "W_DATE": null
        }
      ],
      "INSURANCE": [

      ]
}
]
  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    4 years ago

    Try this:

    $data.mapValues((v,k)=>v.map(x=>x.mapValues((v1,k1)=>k1.endsWith("_DATE") && v1 == null ? "0000-00-00" : v1 )))

    • aditya_gupta41's avatar
      aditya_gupta41
      Contributor
      4 years ago

      This too is getting error:

      failure:

      “Map type does not have a method named: map, found in: …-00-00” : v1 )). Perhaps you meant: mapKeys, mapValues"

      • bojanvelevski's avatar
        bojanvelevski
        Valued Contributor
        4 years ago

        Well, that means that the fields TIMEDEPENDENTDATA,SELECTIONCRITERIA, etc. are objects, not arrays like in the sample you sent.