Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Using multiple datasets in a condition within Modify user data json - Workday RAAS

Arita
New Contributor II
New Contributor II

Hi Team,

Need your inputs for the below usecase.

We have 2 datasets Dataset1,Dataset2 and we have to populate the value in CP42 based on conditions on these datasets as below

CP18 in Dataset1 then return exmaple.com

CP52 in Dataset2 then return example.com

Tried using multiple combinations of queries, it's not working. Samples are below.

1st:"UPDATE NEWUSERDATA JOIN CURRENTDataset1 ON NEWUSERDATA.CUSTOMPROPERTY18 = Dataset1.ATTRIBUTE4 SET NEWUSERDATA.CUSTOMPROPERTY42 = Dataset1.ATTRIBUTE1" or 
"UPDATE NEWUSERDATA JOIN CURRENTDataset2 ON NEWUSERDATA.CUSTOMPROPERTY52 = Datset2.ATTRIBUTE4 SET NEWUSERDATA.CUSTOMPROPERTY42 = Dataset2.ATTRIBUTE1";

2nd:

"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY42 = CASE WHEN (NEWUSERDATA.CUSTOMPROPERTY18 = Dataset1.ATTRIBUTE4) THEN Dataset1.ATTRIBUTE1 WHEN (NEWUSERDATA.CUSTOMPROPERTY52 = Dataset2.ATTRIBUTE4) THEN Dataset2.ATTRIBUTE1 ELSE '' END"

 

Thanks

 

 

5 REPLIES 5

rushikeshvartak
All-Star
All-Star

UPDATE NEWUSERDATA
LEFT JOIN CURRENTDataset1 ON NEWUSERDATA.CUSTOMPROPERTY18 = CURRENTDataset1.ATTRIBUTE4
LEFT JOIN CURRENTDataset2 ON NEWUSERDATA.CUSTOMPROPERTY52 = CURRENTDataset2.ATTRIBUTE4
SET NEWUSERDATA.CUSTOMPROPERTY42 = CASE
WHEN CURRENTDataset1.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset1.ATTRIBUTE1
WHEN CURRENTDataset2.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset2.ATTRIBUTE1
ELSE ''
END;


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Arita
New Contributor II
New Contributor II

Thank you for the reply. We wanted to test this query as well but before that only we started getting error with original JSON itself as below. Would you have an idea on what would have gone wrong and how to fix it. We raised a ticket also. 

{java.lang.NumberFormatException: For input string: "1,,,,,,,,,,,,,,,,,,,,,,,,,," at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.lang.Integer.parseInt(Integer.java:580) at java.lang.Integer.valueOf(Integer.java:766) at com.saviynt.custom.process.ABCCustomTransform.readProperties(ABCCustomTransform.java:111) at com.saviynt.custom.process.ABCCustomTransform.doCustomPreprocessPrimaryEmailID(ABCCustomTransform.java:674) at com.saviynt.ecm.services.ImportSAvDataUserService.callCustomPreProcessorMethod(ImportSAvDataUserService.groovy:562) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:407) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8696) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1720) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:155) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)}

Arita
New Contributor II
New Contributor II

Hi Rushikesh,

We tested with the below query, but it is only working for CP18 condition and for second condition it is not working and moving to next logic in JSON. Any thoughts further ?

UPDATE NEWUSERDATA
LEFT JOIN CURRENTDataset1 ON NEWUSERDATA.CUSTOMPROPERTY18 = CURRENTDataset1.ATTRIBUTE4
LEFT JOIN CURRENTDataset2 ON NEWUSERDATA.CUSTOMPROPERTY52 = CURRENTDataset2.ATTRIBUTE4
SET NEWUSERDATA.CUSTOMPROPERTY42 = CASE
WHEN CURRENTDataset1.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset1.ATTRIBUTE1
WHEN CURRENTDataset2.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset2.ATTRIBUTE1
ELSE ''
END;

Share full json


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Arita
New Contributor II
New Contributor II

Hi Rushikesh,

Jar file issue is resolved. So logic is working now. thank you.