I have a functional query rule that returns the datasubset I am interested in. W

Hello,

Is there a way to merge two datasubsets on the basis of common value in each of them? for example, Suppose I have a datasubset1 containing the value
{UserName: "Smith",
Position: "Manager",
}

and datasubset2 contains

{UserName: "Laura"
Age : 35
},
{UserName: "Smith",
Age:40
}

Now in this case I want the record of "Smith" which is in another datasubset to get appended with the similar value in datasubset1

i.e My output should be

{UserName: "Smith"
Position: "Manager"
Age:40
},
{UserName: "Laura"
Age: 35 // As there is no common record exist for user "Laura" in another subset.
}

Please help me on this. Thanks in advance

Usually i'd suggest starting a new topic for this question since it doesn't really have much in common with the use case being asked about in the original thread from 2 years ago.

However I believe I have a workable solution (assuming you're using 17.2 or newer), so here goes:

with(
  
  local!dataset1: {
    {
      UserName: "Smith",
      Position: "Manager"
    },
    {
      UserName: "Johnson",
      Position: "Cashier"
    },
    {
      UserName: "Apple",
      Position: "Merchandise"
    }
  },
  
  local!dataset2: {
    {
      Name: "Smith",
      Age: 40
    },
    {
      Name: "Johnson",
      Age: 23
    },
    {
      Name: "Baker",
      Age: 54
    }
  },
  
  a!forEach(
    local!dataset1,
    {
      NewDatasetName: fv!item.UserName,
      NewDatasetPosition: fv!item.Position,
      NewDatasetAge: with(
        local!dataset2Index: index(where(local!dataset2.Name = fv!item.UserName), 1, null()),
        if(
          isnull(
            index(
              local!dataset2.Age,
              local!dataset2Index,
              null()
            )
          ),
          "N/A",
          local!dataset2[local!dataset2Index].Age
        )
      )
    }
  )
)

Note that this example iterates over "dataset1" and creates a new dictionary for only the entries found in it.  If there are any entries in "dataset2" not mentioned in the other one, they will not be reflected in the new dictionary.

 

Results:

Hi Mike,

Thank you so much for your response. Its working.. Thanks again

Hi Mike,

Thank you so much for your response. Its working.. Thanks again