Transform data after using Data Loading activity

Avatar

Avatar

Souradeep

Avatar

Souradeep

Souradeep

15-09-2017

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Vapsy

Employee

Avatar

Vapsy

Employee

Vapsy
Employee

16-09-2017

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

Answers (3)

Answers (3)

Avatar

Avatar

Souradeep

Avatar

Souradeep

Souradeep

16-09-2017

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

Thanks once again!

Avatar

Avatar

Souradeep

Avatar

Souradeep

Souradeep

16-09-2017

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

Avatar

Vapsy

Employee

Avatar

Vapsy

Employee

Vapsy
Employee

15-09-2017

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