PreProcessor in SAV file - Manager Value Disappearing

[sab2]

Hi,

We are working on our SAV file for user imports. In our preprocessor, we are trying to set the users attribute 'manager' equal to the users attribute 'secondarymanager' only if the users manager is currently null. If the user in Saviynt does already have a manager then we want to leave it as is with the current value. We are using this line: 

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when CURRENTUSERS.MANAGER is not null and CURRENTUSERS.MANAGER != '' then CURRENTUSERS.MANAGER WHEN (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end",

In our mapping line we only have firstname, lastname, employeeid, secondarymanager. We do not map manager. However, when we run user import job, the query is only half working. If the users current manager is null, it sets the manager equal to secondarymanager. But if the user does have a manager and we run the query again, it takes out the users manager. Leaving this field as blank in Saviynt. Any thoughts on what is causing it to wipe out the manager value? When we use the same query for firstname (below query) it works as expected. Only seeing issues with the manager attributes.

Working: UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.FIRSTNAME = case when CURRENTUSERS.FIRSTNAME is not null and CURRENTUSERS.FIRSTNAME != '' then CURRENTUSERS.FIRSTNAME WHEN (CURRENTUSERS.FIRSTNAME IS NULL or CURRENTUSERS.FIRSTNAME = '') THEN NEWUSERDATA.FIRSTNAME end",

Thank You! 

[Saathvik]

I don't see anything wrong in query but can you try this

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID 
SET NEWUSERDATA.MANAGER = case when CURRENTUSERS.MANAGER is not null then CURRENTUSERS.MANAGER 
WHEN (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end

[sab2]

Thank you for the response. That had the same effect. The manager value is still being wiped out instead of left with current manager value.

[Saathvik]

Can you share the sample of your input file?

[rushikeshvartak]

Please share full preprocessor json

[sab2]

Thank you. I attached sample of the user input feed.

SAV:

#MODIFYUSERDATAJSON={"ADDITIONALTABLES": {"USERS": "SELECT lastname,systemUserName,firstname, username, CUSTOMPROPERTY31, secondaryManager, MANAGER, LOCATION, PREFEREDFIRSTNAME,employeeid FROM USERS"},"COMPUTEDCOLUMNS": ["firstname","username","secondaryManager", "MANAGER","employeeid"],"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA N JOIN CURRENTUSERS U ON N.EMPLOYEEID=U.EMPLOYEEID SET N.USERNAME=U.USERNAME","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when CURRENTUSERS.MANAGER is not null then CURRENTUSERS.MANAGER WHEN (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.FIRSTNAME = case when CURRENTUSERS.FIRSTNAME is not null and CURRENTUSERS.FIRSTNAME != '' then CURRENTUSERS.FIRSTNAME WHEN (CURRENTUSERS.FIRSTNAME IS NULL or CURRENTUSERS.FIRSTNAME = '') THEN NEWUSERDATA.FIRSTNAME end"]}
EMPLOYEEID,LASTNAME,FIRSTNAME,MIDDLENAME,CustomProperty29,CustomProperty30,CustomProperty9,CustomProperty8,CustomProperty38,SECONDARYMANAGER,CustomProperty3

[mbinsale]

Please try the below since you do not want the existing manager entry to modified if it already exists

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end

[sab2]

Hi,

Thank you for the response. I tried this out in the SAV file and it did not work. The manager was still wiped out.

[mbinsale]

Could you try this please

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER else CURRENTUSERS.MANAGER end

[sab2]

Hi, Thank you. I tested this out as well and still no luck. The manager field continues to be wiped out.

[mbinsale]

This is verified to be working in my local database. Seems to me a data related issue causing this issue. Could you please try this in any other database. This is an extremely straightforward SQL 

[timchengappa]

Hi @sab2 

Please use the below PREPROCESSQUERIES...

"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.OWNER = case when (CURRENTUSERS.OWNER IS NULL or CURRENTUSERS.OWNER = '') THEN NEWUSERDATA.SECONDARYMANAGER else CURRENTUSERS.OWNER end where NEWUSERDATA.OWNER IS NULL or NEWUSERDATA.OWNER = ''"]}

[sab2]

Thank you. This was tested however we are still experiencing the same result. With this query now the manager field and the owner field are being wiped out.

[timchengappa]

Hello @sab2 

Please use the attached files '.csv' & '.sav' files and let me know if it resolved your issues...