We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Preprocessor not working while user creation from CSV upload

gauravchandok
New Contributor III
New Contributor III

We are trying to write a preprocessor for user import via csv or via workday.

The requirement is if the users's cp40 is xyz company then his cp20 should be @xyz.com else if his cp40 is abc systems then his cp20 should be @abccom.com and so on.

Below is the preprocessor I tried testing via csv upload which did not worked as user did not get created.

 

{
   "ADDITIONALTABLES":{
      "USERS":"SELECT CUSTOMPROPERTY20,CUSTOMPROPERTY40,USERNAME from USERS"
   },
   "COMPUTEDCOLUMNS":[
      "CUSTOMPROPERTY20",
      "CUSTOMPROPERTY40"
   ],
   "PREPROCESSQUERIES":[
      "UPDATE NEWUSERDATA n LEFT JOIN CURRENTUSERS c ON n.USERNAME = c.USERNAME SET n.CUSTOMPROPERTY20=CASE WHEN n.CUSTOMPROPERTY40 = 'xyz capital' THEN '@xyz.com' WHEN n.CUSTOMPROPERTY40 = 'abc system' THEN '@abc.com' WHEN n.CUSTOMPROPERTY40 = 'starpick' THEN '@start.com' WHEN n.CUSTOMPROPERTY40 = 'kww group' THEN '@kww.com' WHEN (n.CUSTOMPROPERTY40 = 'xyz Offshore' or n.CUSTOMPROPERTY40 = 'abcna offshore') THEN '@hghh.com' else '@abcd.com' end"
   ]
}

 

and my csv file content is below:

 

statuskey,departmentname,firstname,lastname,CUSTOMPROPERTY40
1,,pintu,Kumar,abc system

 

Need your help in resolving the issue in preprocessor, issue is similar to this post below

https://forums.saviynt.com/t5/identity-governance/pre-processor-does-not-work-while-creating-a-user-...

4 REPLIES 4

Sivagami
Valued Contributor
Valued Contributor

Try this

 

UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 = (CASE WHEN CUSTOMPROPERTY40 = 'xyz capital' THEN '@xyz.com' WHEN CUSTOMPROPERTY40 = 'abc system' THEN '@abc.com' WHEN CUSTOMPROPERTY40 = 'starpick' THEN '@start.com' WHEN CUSTOMPROPERTY40 = 'kww group' THEN '@kww.com' WHEN( CUSTOMPROPERTY40 = 'xyz Offshore' OR CUSTOMPROPERTY40 = 'abcna offshore') THEN '@hghh.com' ELSE '@abcd.com' END)

 

-Siva

SumathiSomala
All-Star
All-Star

@gauravchandok try below

"UPDATE NEWUSERDATA n LEFT JOIN CURRENTUSERS c ON n.USERNAME = c.USERNAME SET n.CUSTOMPROPERTY20=CASE WHEN c.CUSTOMPROPERTY40 = 'xyz capital' THEN '@xyz.com' WHEN c.CUSTOMPROPERTY40 = 'abc system' THEN '@abc.com' WHEN c.CUSTOMPROPERTY40 = 'starpick' THEN '@start.com' WHEN c.CUSTOMPROPERTY40 = 'kww group' THEN '@kww.com' WHEN (c.CUSTOMPROPERTY40 = 'xyz Offshore' or c.CUSTOMPROPERTY40 = 'abcna offshore') THEN '@hghh.com' else '@abcd.com' END"

 

 

Normalizing the Identity Data Using the User Import Preprocessor (saviyntcloud.com)

Regards,
Sumathi Somala
If this reply answered your question, please Accept As Solution and give Kudos.

gauravchandok
New Contributor III
New Contributor III

Dear all,

I have solved it like this for now.. will test the suggestion in sometime and update.. for now below is solution

{
   "ADDITIONALTABLES":{
      "USERS":"select * from users"
   },
   "COMPUTEDCOLUMNS":[
      "CUSTOMPROPERTY10",
      "CUSTOMPROPERTY20"
   ],
   "PREPROCESSQUERIES":[
      "UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY20 = (CASE WHEN NEWUSERDATA.CUSTOMPROPERTY10 ='xyz capital'  THEN '@abc.com' ELSE (CASE WHEN NEWUSERDATA.CUSTOMPROPERTY10 ='abc system' THEN '@abc.com' ELSE (CASE WHEN NEWUSERDATA.CUSTOMPROPERTY10 ='starpick' THEN '@start.com' ELSE (CASE WHEN NEWUSERDATA.CUSTOMPROPERTY10 ='kww group' THEN '@kww.com' ELSE (CASE WHEN (NEWUSERDATA.CUSTOMPROPERTY10 ='xyz Offshore' or NEWUSERDATA.CUSTOMPROPERTY10 ='abcna offshore') THEN '@hghh.com' ELSE '@abcd.com' END) END) END) END) END);"
   ]
}

thanks

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY20, CUSTOMPROPERTY40, USERNAME FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY20",
"CUSTOMPROPERTY40"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA n LEFT JOIN CURRENTUSERS c ON n.USERNAME = c.USERNAME SET n.CUSTOMPROPERTY20 = CASE WHEN n.CUSTOMPROPERTY40 = 'xyz capital' THEN '@xyz.com' WHEN n.CUSTOMPROPERTY40 = 'abc system' THEN '@abc.com' WHEN n.CUSTOMPROPERTY40 = 'starpick' THEN '@start.com' WHEN n.CUSTOMPROPERTY40 = 'kww group' THEN '@kww.com' WHEN (n.CUSTOMPROPERTY40 = 'xyz Offshore' OR n.CUSTOMPROPERTY40 = 'abcna offshore') THEN '@hghh.com' ELSE '@abcd.com' END"
]
}