Transform data after using Data Loading activity

[Souradeep]

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

[Vipul_Raghav]

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.

Hope this helps.

Regards,
Vipul

[Souradeep]

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

[Vipul_Raghav · Accepted solution]

Hi SouraDeep,

When you are picking a blank string

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

[Souradeep]

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

Thanks once again!