Create generation email rule via dataset

[JPMac]

I am creating generation emal rule.

I put all the old addresses and usernames in the dataset(follow picture), and prepend 'old' to emailaddress if the user's username is in attribute2 (username) of the dataset.
Otherwise, just username@domain.

For example, to create username'M12827018' would be 'oldm12827018@example.com' and to create 'M12827019' not in dataset would be 'm12827019@example.com'.
The advanced config I created is:

===

case when users.username != (select distinct attribute2 from dataset_values where datasetname='oldemail') then concat('old', users.username, '@example.com') else concat(users.username, '@example.com') end

===

However, this rule does not work, user's email is a blank.
Can't the email generation rule read the dataset anyway?
Or let me know if my rule is written wrong.

Regards,

[amit_krishnajit]

@JPMac - The above query would not work because the sub-query used by you "select distinct attribute2 from dataset_values where datasetname='oldemail'" would always return more than one record and you are validating multiple records with username. 

You may want to update your SQL to something like the following and try it out: 

case when users.username in (select distinct attribute2 from dataset_values where datasetname='oldemail' and attribute2=users.username) then concat('old', users.username, '@example.com') else concat(users.username, '@example.com') end

[JPMac]

Hi @amit_krishnajit 

Thanks for your response, and I tried it.

===

case when users.username in (select distinct attribute2 from dataset_values where datasetname='oldemail' and attribute2=users.username) then concat('old', users.username, '@example.com') else concat(users.username, '@example.com') end

===

However, the address of the user named in the dataset will also be username@example.com and will not be old.　Here is the contents of the dataset.

Here is the user who uploaded this CSV file.

Here is the email address of the result of the upload.

*mask is our real domain and managername.

Could you advise me how to check the contents of case when?

[amit_krishnajit]

I don't understand your requirement. Can you please elaborate on it further with a clear example?

[JPMac]

@Amit - let me explain an example.

In the image above, three users (M12827066, M12827067, M12827068) have been uploaded.
In the dataset, M12827068 is included, M12827066 and M12827067 are not.

In this case, the purpose is to generate the following email address:
M12827066 : m12827066@example.com
M12827067 : m12827067@example.com
M12827068 : oldm12827068@example.com

However, even with the above rule, the email address of M12827068 will be m12827068@example.com.

[amit_krishnajit · Accepted solution]

Got the full picture now. I think the following JSON should work fine for you. 

{
	"ADDITIONALTABLES": {
		"DATASET_VALUES": "SELECT datasetname,attribute1,attribute2 FROM DATASET_VALUES WHERE datasetname='Old-Email-Info';"
	},
	"COMPUTEDCOLUMNS": [
		"email"
	],
	"PREPROCESSQUERIES": [
		"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTDATASET_VALUES CDV ON NU.USERNAME=CDV.attribute1 SET NU.EMAIL=IFNULL(CDV.attribute2, CONCAT(NU.USERNAME,'@example.org'))",
	]
}