Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
07/09/2024
01:33 AM
- last edited on
07/10/2024
08:12 AM
by
Dave
Hi all,
I am using a Dataset where I have 2 columns named "Location" and "Address" as shown below.
Example Dataset:
I need to compare the user's "location" with "attribute1" and need to update the "CUSTOMPROPERTY65" with the values in "attribute2".
We need to bring all the addresses into the user's CUSTOMPROPERTY65 by CONCATINATING all the addresses in the 'attribute2' of the Dataset where attribute1= India.
The below pre-processor works only when a country was mentioned only once in attribute1.
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS",
"DATASET_VALUES": "SELECT attribute1,attribute2,datasetname from DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"LOCATION","CUSTOMPROPERTY16"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1=CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.LOCATION = (select attribute2 from CURRENTDATASET_VALUES where datasetname = 'Country_Location' and attribute1 = NEWUSERDATA.location)"
]
}
Can someone please help on how to CONCATENATE the values in 'attribute2' using the pre-processor.
Thanks & Regards,
SaviyntSavvy
[This post has been edited by a Moderator to remove sensitive information.]
Solved! Go to Solution.
07/09/2024 02:03 AM - edited 07/09/2024 02:04 AM
Try below
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS",
"DATASET_VALUES": "SELECT attribute1,attribute2,datasetname from DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"LOCATION","CUSTOMPROPERTY16"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1=CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.LOCATION = (select group_concat(attribute2 SEPARATOR ' ') from CURRENTDATASET_VALUES where datasetname = 'Country_Location' and attribute1 = NEWUSERDATA.location)"
]
}
Here separator is space, in case you want any other character as separator, you can provide.
07/12/2024 03:09 AM
Hi @pmahalle
Thank you for the solution.
It is working as expected. Meanwhile, could you please help me on the like wise scenario in the below forum post.
https://forums.saviynt.com/t5/identity-governance/dataset-evaluation-in-create-ticket-json/m-p/10455...
Thanks & Regards,
Saviynt Savvy