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

Ignore blank field through pre-processor user upload - Update scenario

Shubhamjain27
New Contributor III
New Contributor III

Hi,

We have a usecase where we are providing a set of fields in a csv file to customer and it will be used to update existing attributes.

in csv we have 4 fields

username, firstname, lastname, empid.

Now the customer wants to update firstname based on the username and if the customer keeps lastname and empid field blank, it will update the firstname with the correct name and make empid,lastname blank.

Is it possible to ignore the blank values and dont update the fields?

If yes, how? if its done through pre-processor, please help me with the sample

3 REPLIES 3

PremMahadikar
Valued Contributor
Valued Contributor

Hi @Shubhamjain27 ,

Below is the working preprocessor script:

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,firstname,lastname,employeeid FROM USERS"
},
"COMPUTEDCOLUMNS": [
"firstname",
"lastname",
"employeeid",

],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS  ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.firstname = CASE WHEN CURRENTUSERS.firstname IS NOT NULL OR CURRENTUSERS.firstname!='' THEN CURRENTUSERS.firstname ELSE NEWUSERDATA.firstname END",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS  ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.lastname = CASE WHEN CURRENTUSERS.lastname IS NOT NULL OR CURRENTUSERS.lastname!='' THEN CURRENTUSERS.lastname ELSE NEWUSERDATA.lastname END",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS  ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.employeeid = CASE WHEN CURRENTUSERS.employeeid IS NOT NULL OR CURRENTUSERS.employeeid!='' THEN CURRENTUSERS.employeeid ELSE NEWUSERDATA.employeeid END"
]
}

FYI: My csv file has columns: USERNAME,firstname,lastname,employeeid

 

If this answers your question, please consider selecting Accept As Solution and hit Kudos

Shubhamjain27
New Contributor III
New Contributor III

The above query is working but there's one issue.

If the lastname already exists for a user 'test1' and in the new csv if we put 'test12'. It is not changing to test12.

PremMahadikar
Valued Contributor
Valued Contributor

@Shubhamjain27 ,

The query will slightly change.

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS  ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.lastname = CASE WHEN NEWUSERDATA.lastname IS NULL OR NEWUSERDATA.lastname='' THEN CURRENTUSERS.lastname ELSE NEWUSERDATA.lastname END"

Please update the same for firsname and employeeid.

 

If this helps, please consider selecting Accept As Solution and hit Kudos