-
1. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Mohammad Khan Jan 25, 2021 8:00 AM (in response to Vishal Singh)Hi Vishal,
There are a couple of knowledge base articles available on our website on iterating over a list:
https://knowledge.informatica.com/s/article/577264
https://knowledge.informatica.com/s/article/533581
https://knowledge.informatica.com/s/article/478024
I suggest you take a look at these articles.
This is a custom use case. So, some level of xquery design \ process design will be needed to come up with the exact implementation.
In case you need further assistance on this use case, I would suggest you open a support ticket so that the support team can look into it.
Hope this helps.
Thanks,
Mohammad
-
2. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Prathap Reddy Jan 25, 2021 8:10 AM (in response to Vishal Singh)You may follow this approach provided if the same number of records are present in both source1 and source2 also in the same order.
Create 2 temp variables(t1 and t2) with the type of process object address one for src1 and another for src2
Take list:head() from source1 and source2 in an assignment and assign to the temp variables created above
In the next assignment, use a temp variable(t_check) and check if updated date in t1 is greater than t2 and assign a value 'Y' if yes 'N' if no.
In the decision check if the t_check is 'Y' or not. If 'Y' use a list (t_final)and add t1 to the list and if 'N' add t2 to the list.
The same process should be run in a loop(Head and Tail).
Assign t_final to the output.
Please reach out to me if any issues.
Thanks,
Prathap Reddy.
+91-9989045104.
-
3. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Vishal Singh Jan 25, 2021 11:50 AM (in response to Mohammad Khan)Hi Mohammad,
the resources you have provided were not that helpful. Is there any specific process I need to follow to open up a ticket on this use case.
Can you please suggest.
Thanks,
Vishal
+91-9666384352
-
4. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Vishal Singh Jan 25, 2021 11:52 AM (in response to Prathap Reddy)Hi Prathap,
Source1 and Source2 can be different also can have different sequence. Would like to see if you are aware of any other options to achieve this part.
Thanks,
Vishal
+91-9666384352
-
5. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Mohammad Khan Jan 25, 2021 2:23 PM (in response to Vishal Singh)Hi Vishal,
You can log on to - https://network.informatica.com/welcome and click "access eSupport". Here you can open a support ticket. If you do not have login for the above link, you will first need to register and get a login.
Hope this info helps.
Thanks,
Mohammad
-
6. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Prakash Jain Jan 26, 2021 3:15 AM (in response to Vishal Singh)Hi Vishal / Mohammad,
Please find the sample example I have created to work on simple and smaller dataset. The idea here is you have convert the above JSON into XML using toXML, parseXML etc. function we have within the editor and then apply the logic for picking the latest information and convert the XML back to JSON.
The reason for this conversion from JSON to XML vise versa is to make the logic simpler and XQuery works on XML natively.
Please find the sample here:
let $Source1:=
<EmployeeDetails>
<Emplooyee>
<EmpId>1</EmpId>
<Name>Mary</Name>
<Age>14</Age>
</Emplooyee>
<Emplooyee>
<EmpId>2</EmpId>
<Name>John</Name>
<Age>17</Age>
</Emplooyee>
<Emplooyee>
<EmpId>3</EmpId>
<Name>Nick</Name>
<Age>35</Age>
</Emplooyee>
</EmployeeDetails>
let $Source2:=
<EmployeeDetails>
<Emplooyee>
<EmpId>1</EmpId>
<Name>Mary</Name>
<Age>25</Age>
</Emplooyee>
<Emplooyee>
<EmpId>2</EmpId>
<Name>John</Name>
<Age>12</Age>
</Emplooyee>
<Emplooyee>
<EmpId>3</EmpId>
<Name>Nick</Name>
<Age>21</Age>
</Emplooyee>
</EmployeeDetails>
for $i in $Source1//Emplooyee
let $source1Age:= $i/Age
let $source2Age:= $Source2//Emplooyee[EmpId=$i/EmpId]/Age
return
if($source1Age > $source2Age ) then
$i
else
$Source2//Emplooyee[EmpId=$i/EmpId]
If this helps, mark this thread as resolved.
Thanks,
Prakash Jain
-
7. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Vishal Singh Jan 26, 2021 10:56 PM (in response to Prakash Jain)Hi Prakash Jain,
The image which you attached is not clear and very small. can you please attach bigger image and clearer one?
Thanks
Vishal
+91-9666384352
-
8. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Vishal Singh Jan 27, 2021 10:51 AM (in response to Prakash Jain)Hi Prakash,
I tried converting the existing source1 and source2 JSON to XML, but it was throwing errors.
First attempt, i took temp_input_json and tried with toXML and it gave me below error:
Error executing expression (
{ util:toXML( iomap:getValue(
iomap:getElement($temp/child::element()[sff:normalizeField(@name)=sff:normalizeField('temp_input_json')]))) }
). Reason: Required item type of first argument of util:toXML() is element(); supplied value has item type text()
Second attempt i took temp_input_json and tried with parseXML and then toXML and it gave me below error:
Error executing expression (
{ util:toXML(util:parseXML( iomap:getValue(
iomap:getElement($temp/child::element()[sff:normalizeField(@name)=sff:normalizeField('temp_input_json')])))) }
). Reason: Error evaluating function xmlStringToElement - org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog.
Third attempt i took temp_input_json and tried with toXML and then parseXML and it gave me below error:
Error executing expression (
{ util:parseXML(util:toXML( iomap:getValue(
iomap:getElement($temp/child::element()[sff:normalizeField(@name)=sff:normalizeField('temp_input_json')])))) }
). Reason: Required item type of first argument of util:toXML() is element(); supplied value has item type text()
Can you please guide me on how to proceed. I have even opened a support ticket [02987579] as suggested by someone.
I work during IST hours(12 pm to 9 pm) and it would be of great help if I could get this done.
Thanks
Vishal
+91-9666384352
-
9. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Vishal Singh Feb 22, 2021 6:59 AM (in response to Vishal Singh)Hi Prakash Jain,
I have got the source1 and source2 values, converted the json format into xml formats.
In the assignment fields I converted to xml using below and it did convert to XML.
util:toXML(util:parseJSON($temp.v_json_value_xquery_txt_2 ))
util:toXML(util:parseJSON($temp.v_json_value_xquery_txt_1a ))
In the next subsequent fields within the same assignment, I have added the below code just to see if the data fields are coming irrespective of the age conditions.
let $Source1:= $temp.v_json_value_xquery_txt_2
let $Source2:= $temp.v_json_value_xquery_txt_1a
for $i in $Source1//Emplooyee
return $i
But i was not getting any values.
Later I hard-coded as per your example shown above, it worked.
Not sure, when i am passing dynamically without hard-coding after converting to XML it is not working. It only works for the hard-coded example.
Can you please throw some light on it, if there is anything extra to be added in the XQUERY code.
-
10. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Prakash Jain Feb 25, 2021 4:22 AM (in response to Vishal Singh)Hi Vishal,
Do you still have issue ? Can you please share the above JSON payload?
Alternatively you can raise a Support Case with us to look into as it needs investigation on what going wrong.
Thanks,
Prakash Jain
-
11. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Vishal Singh Feb 25, 2021 4:49 AM (in response to Prakash Jain)Hi Prakash,
I am not sure if I can provide the exact payload due to security reasons. I have indeed opened a support-case : Case: 02987579 , but it was not helpful.
I tried many different ways to get it, but could not succeed.
So should I open another support case, or will you be able to help me with solving this issue. Please advise.
Thanks,
Vishal
+91-9666384352
-
12. Re: Iterate from Source 1 Json and Source 2 Json and update whichever array element is greater
Prakash Jain Feb 25, 2021 5:17 AM (in response to Vishal Singh)Thanks will look into it.