Preprocessor to Update users not in file

[yashpalsingh]

Hi Folks,

How do I calculate customproperty of users during user import if they are not in the file from HR feed?

Regards,

Yashpal

[amit_krishnajit]

Use the Additional table query for users table to pull all the users which match the sourcekey of the current source. Based on that use update queries in preprocessor queries to determine if the records are not present in newuserdata table vs present in currentusers table and map a value to the CP.

[yashpalsingh]

I was trying this but it doesnt update the CP64 of users not present in file. Is there a way to do the calculation even if the users not present in the file?

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,LOCATION,CUSTOMPROPERTY46,CUSTOMPROPERTY47,CUSTOMPROPERTY64,ENDDATE FROM USERS",
"DATASET_VALUES": "SELECT attribute1,attribute2,attribute3,DATASETNAME FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY46","CUSTOMPROPERTY47","CUSTOMPROPERTY64"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY46 = (select attribute2 from CURRENTDATASET_VALUES where DATASETNAME = 'OUMapping' and attribute1 = NEWUSERDATA.location)",
"UPDATE CURRENTUSERS SET CURRENTUSERS.CUSTOMPROPERTY64 = 'Phase2Termination' where CURRENTUSERS.ENDDATE <= curdate()"
]
}

[rushikeshvartak]

use INNER join

[yashpalsingh]

Like This? Doesnt update though

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,LOCATION,CUSTOMPROPERTY46,CUSTOMPROPERTY47,CUSTOMPROPERTY64,ENDDATE FROM USERS",
"DATASET_VALUES": "SELECT attribute1,attribute2,attribute3,DATASETNAME FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY46","CUSTOMPROPERTY47","CUSTOMPROPERTY64"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY46 = (select attribute2 from CURRENTDATASET_VALUES where DATASETNAME = 'OUMapping' and attribute1 = NEWUSERDATA.location)",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY47 = (select attribute3 from CURRENTDATASET_VALUES where DATASETNAME = 'OUMapping' and attribute1 = NEWUSERDATA.location)",
"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY64 = 'Phase2Termination' where CURRENTUSERS.ENDDATE <= curdate()"
]
}

[rushikeshvartak]

None of the queries worked or last one is not working ?

[yashpalsingh]

I want to do calculations on the users even if the user is not present in the file, hence using this. User is not present in the file but I want do some calculation on it.

"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY64 = 'Phase2Termination' where CURRENTUSERS.ENDDATE <= curdate()"

[amit_krishnajit]

Please note that if the records are not there in the file(source), they would not be part of the newuserdata table. You can pull those records based on user source attribute in users table and insert them into newuserdata table. After you have inserted those data, you may use update query to stamp the users that are not part of the file as such. Hope that clarifies.