Cleaning Data - How to change content of a column based on values in another column

[shishir_kumar3]

I have a data set containing the following (sample):

Code	Date	Name	Color	Size	Qty	Price
890211100001	10.02.2014	XYZ	BLACK	39	1	1885
890211100001	11.02.2014	XYZ	BLACK	39	1	1885
890212121001	15.02.2014	XYZ	BLACK	39	1	1885
1200100XYZ01391800	19.03.2014	XYZ	BLACK	39	1	1885
1200100XYZ01391800	27.04.2014	XYZ	BLACK	39	1	1885
89021110001	23.05.2014	XYZ	BLACK	39	2	1885

The Code column contains incorrect entries. The Correct Code is 890211100001.

How do I replace all incorrect codes based on Column "Name", "Color", "Size". (i.e. if Name = "XYZ" and Color = "BLACK" and Size = 39 then replace all code with "890211100001" ?

I can do it in excel using formulas but how to do this in Lumira?

Regards

Shishir

[TammyPowlas]

I looked at Replace - but that won't work because you have multiple columns

The best I can come up with is creating a calculated dimension - in my case I called it "Clean"

Here is the formula:

if {Color} = "BLACK" and {Name} = "XYZ" and {Size} = 39 then "890211100001" else {Code}

Here are the results:

If I have time I'll try to document in a document the steps.

[shishir_kumar3]

Hi Tammy,

Thanks for the quick response.

My problem is not limited to only one SKU

In a data set containing approximately 1 million rows, around 10k rows have this error for different name/color/size combination. So hardcoding for each sku is not going to be a practical solution.

regards

Shishir

[TammyPowlas]

In that case, moving the cleansing part might be better served by a tool like Data Services, which has lookups, transformations, etc.