CONCATENATE the attribute values in a DATASET using Pre-Processor.

[Saviynt_Savvy]

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.]

[pmahalle · Accepted solution]

@Saviynt_Savvy 

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.

[Saviynt_Savvy]

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