Expand my Community achievements bar.

SOLVED

Transform data after using Data Loading activity

Avatar

Level 3

Hi Experts,

I am relatively new to Campaign and gradually getting acquainted with the tool. I have a very simple requirement, trying to understand if there is an easier way to solve this problem. I have a Data Loading activity followed by an Update Activity.

A column from the Data Loading Activity needs to be transformed before pushing to the Update Activity.

So I am using an Enrichment activity in between the two.

However when it comes to adding an expression in the Enrichment activity, I am struggling as how to achieve the following:

If(column1 not equals value1 and  column1 not equals value2) {

    set the value of column 1 to value 3;

}

Please advise.

Thanks!

Souradeep

1 Accepted Solution

Avatar

Correct answer by
Employee

Hi SouraDeep,

When you are picking a blank string 1300197_pastedImage_0.png

it is transforming that to a NULL value at backend

INSERT INTO wkf77568_15_1 (sMyColumn,iId) SELECT  DISTINCT  NULL, R0.iRecipientId FROM NmsRecipient R0 WHERE (R0.iRecipientId = 7000) AND ((R0.iRecipientId > 0 OR R0.iRecipientId < 0))

So you will use the existing formula, NULL comparison will not hold true. The test condition will always return false and hence your existing lang will be selected.

You will have to modify your formula.

Iif(NoNull(@myColumn) != 'en' && NoNull(@myColumn) !='fr', 'cn', @myColumn)

Regards,
Vipul

View solution in original post

4 Replies

Avatar

Employee

Hi Souradeep,

You can use this formula

Iif(@myColumn != 1 && @myColumn !=2, 3, 4)

So the above condition check if value of myColumn is not equal to 1 and 2 then the output will be 3 otherwise 4.

Now you also had a requirement that the output should be applied to the original field that is myColumn then you will have to apply the alias.

1299993_pastedImage_1.png

Hope this helps.

Regards,
Vipul

Avatar

Level 3

Thanks for your response Vipul, I applied the same now but it looks like the (!=) operator is not valid for string fields.

Can you confirm the corresponding operator for the same?

I applied this: Iif(lang != 'en' && lang != 'fr', 'en' , lang) and basically  it kept the original value of the lang field even for the records with blank value for lang, it should have been populated with 'en'.

Thanks,

Souradeep

Avatar

Correct answer by
Employee

Hi SouraDeep,

When you are picking a blank string 1300197_pastedImage_0.png

it is transforming that to a NULL value at backend

INSERT INTO wkf77568_15_1 (sMyColumn,iId) SELECT  DISTINCT  NULL, R0.iRecipientId FROM NmsRecipient R0 WHERE (R0.iRecipientId = 7000) AND ((R0.iRecipientId > 0 OR R0.iRecipientId < 0))

So you will use the existing formula, NULL comparison will not hold true. The test condition will always return false and hence your existing lang will be selected.

You will have to modify your formula.

Iif(NoNull(@myColumn) != 'en' && NoNull(@myColumn) !='fr', 'cn', @myColumn)

Regards,
Vipul

Avatar

Level 3

Thank you so much Vipul for the quick response, this is working now!

Thanks once again!