To get specific values from Json array in a cell/column in DataTable based on value in other column

[TrinadhB (Boda Trinadh)]

Hi
i have shared both the input and expected output files. Hoping this will help to figure out the logic. Here Input file data is extracted from orchestrator which also contains the DateTime at which the item is processed. Among the duplicates Based on the time we need to get the latest record.
SampleInput_recon.xlsx (10.2 KB)
SampleOutput.xlsx (8.9 KB)

[Nithinkrishna]

Hey @TrinadhB

You can please directly use the Deserialize Json Array activity to convert it to a DataTable.

Thanks
#nK

[mitesh_parmar]

Hi @TrinadhB ,

You can do one thing.
fetch Col2(Json) with reference with Col1, Once u get Col2 value.
Pass same value into Deserialize json array Activity.
from that Json-array you can try any array logic to fetch required data.
like wise (loop or Enumerate on array)

tx

[ppr (Peter Preuss)]

As mentioned above we can deserialize to a datable:

@TrinadhB

in col2 we have normal string values, based on particular string values in col2 i need to get specific values from json object in col1 by splitting json object to individual rows

unclear on this part as we do not see samples. But as we have a datable in place we can process as regular (Activities, LINQ, …)

[TrinadhB (Boda Trinadh)]

consider the above case, based on person in col1 and age in col3, is there any syntax or expression for getting corresponding checks, their statuses from output into another column

[supermanPunch (Arpan)]

@TrinadhB

for getting corresponding checks

Hi @TrinadhB ,

I do think that the information is still not sufficient.

We do not have a clear understanding of How the Person is mapped to a Certain Status or Check in Output Column’s list of values.

[TrinadhB (Boda Trinadh)]

Hi @supermanPunch

There is no such mapping. Above one is an example but output column can have many like( check, status,x,y,z…).In that, I want specificly for person1 is check value(A,B) and status value(pass/fail) may be in col4 like:
col1 col2 col3 output col4
person1 num age xxx x A-pass, B -fail

[supermanPunch (Arpan)]

@TrinadhB

@TrinadhB ,

I think it would be more clearer, if you could provide an Expected Output Table for the above Input table.

We should be able to identify the logic and give you a better suggestion.

[TrinadhB (Boda Trinadh)]

Hi @supermanPunch
As you mentioned, i have shared both the input and expected output files. Hoping this will help to figure out the logic. Here Input file data is extracted from orchestrator which also contains the DateTime at which the item is processed. Among the duplicates Based on the time we need to get the latest record.
SampleInput_recon.xlsx (10.2 KB)
SampleOutput.xlsx (8.9 KB)