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

Set User's Property Data Based on Current User's Property Data

KK
Regular Contributor
Regular Contributor

Hi.

When importing users from csv file(update user from csv file), I want to set user's property as following.

  • IF existing user's customproperty63 is NULL, set user's customproperty64 as "NEW"
  • IF existing user's customproperty63 value is same as imported customproperty63 value, set user's customproperty64 as "No Updated"
  • IF existing user's customproperty63 value is different from imported customproperty63, set user's customproperty64 as "Updated"

I set MODIFYUSERDATAJSON as below, but user is not updated.

{"ADDITIONALTABLES":{"USERS":"SELECT * FROM USERS"},"COMPUTEDCOLUMNS":["customproperty64"],"PREPROCESSQUERIES":["update newuserdata set newuserdata.customproperty64 = CASE WHEN currentuserdata.customproperty63 IS NULL THEN 'NEW' WHEN currentuserdata.customproperty63 = newuserdata.customproperty63 THEN 'No Updated' ELSE 'Updated' END"]}

How to realize this?

Best Regards,

11 REPLIES 11

rushikeshvartak
All-Star
All-Star

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": ["customproperty64"],
"PREPROCESSQUERIES": [
"UPDATE newuserdata LEFT JOIN currentuserdata ON currentuserdata.username = newuserdata.username SET newuserdata.customproperty64 = CASE WHEN currentuserdata.customproperty63 IS NULL THEN 'NEW' WHEN currentuserdata.customproperty63 = newuserdata.customproperty63 THEN 'No Updated' ELSE 'Updated' END"
]
}


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

KK
Regular Contributor
Regular Contributor

@rushikeshvartak 
Thank you. I try it, but it doesn't work.

Best Regards,

Share logs


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

KK
Regular Contributor
Regular Contributor

@rushikeshvartak 
I don't see valid logs.

KK_0-1717729439846.png

Best Regards,

Raghu
Honored Contributor
Honored Contributor

@KK  try below

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": ["CUSTOMPROPERTY64"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA
SET CUSTOMPROPERTY64 = CASE
WHEN CURRENTUSERDATA.CUSTOMPROPERTY63 IS NULL THEN 'NEW'
WHEN CURRENTUSERDATA.CUSTOMPROPERTY63 = NEWUSERDATA.CUSTOMPROPERTY63 THEN 'NO UPDATED'
ELSE 'UPDATED'
END
FROM CURRENTUSERDATA
WHERE NEWUSERDATA.USERKEY = CURRENTUSERDATA.USERKEY"
]
}


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

KK
Regular Contributor
Regular Contributor

@Raghu 
Thank you, but it doesn't work.

Best Regards,

Raghu
Honored Contributor
Honored Contributor

@KKtry

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": ["CUSTOMPROPERTY64"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA AS N
SET CUSTOMPROPERTY64 = CASE
WHEN C.CUSTOMPROPERTY63 IS NULL THEN 'NEW'
WHEN C.CUSTOMPROPERTY63 = N.CUSTOMPROPERTY63 THEN 'NO UPDATE'
ELSE 'UPDATED'
END
FROM CURRENTUSERDATA AS C
WHERE N.USERKEY = C.USERKEY"
]
}

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Raghu
Honored Contributor
Honored Contributor

Yes Small change

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": ["CUSTOMPROPERTY64"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA AS N
SET CUSTOMPROPERTY64 = CASE
WHEN C.CUSTOMPROPERTY63 IS NULL THEN 'NEW'
WHEN C.CUSTOMPROPERTY63 = N.CUSTOMPROPERTY63 THEN 'NO UPDATE'
ELSE 'UPDATED'
END
FROM CURRENTUSERS AS C
WHERE N.USERKEY = C.USERKEY"
]
}


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT USERNAME,CUSTOMPROPERTY63 FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "CUSTOMPROPERTY64"
  ],
  "TABLEINDEXES": {
    "currentusers": [
      "USERNAME",
      "CUSTOMPROPERTY63"
    ]
  },
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS  ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY64 = CASE WHEN CURRENTUSERS.CUSTOMPROPERTY63 IS NULL THEN 'NEW' WHEN CURRENTUSERS.CUSTOMPROPERTY63 = NEWUSERDATA.CUSTOMPROPERTY63 THEN 'NO UPDATE' ELSE 'UPDATED' END"
  ]
}

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

PremMahadikar
Valued Contributor
Valued Contributor

Hi @KK ,

You are using currentuserdata. (There is no such table called currentuserdata - Table 'ssminlp.currentuserdata' doesn't exist)

Correct table - CURRENTUSERS

Working code:

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY64"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS  ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY64 = CASE WHEN CURRENTUSERS.CUSTOMPROPERTY63 IS NULL THEN 'NEW' WHEN CURRENTUSERS.CUSTOMPROPERTY63 = NEWUSERDATA.CUSTOMPROPERTY63 THEN 'NO UPDATE' ELSE 'UPDATED' END"
]
}

FYI: My CSV file columns - username,cp63,cp64

 

If this helps, please select Accept As Solution and hit Kudos

KK
Regular Contributor
Regular Contributor

@PremMahadikar 
Thank you. It works.

Best Regards,