Magic ETL Help - Overwrite value in a column if another column equals one of two values

swagner

In our sales system there was a recent change to a part number, and I need to correct history in the dataset.  If the 2nd Item Number is either "LOT SQD STOCK" or "LOT SQD SPECIAL" then I want to overwrite the value in the Supplier ID column to be "SQD" otherwise I want the Supplier ID column keep it's current value.

 

I'd like to do this in Magic ETL if I can.

 

Here's the basic logic:

IF 2nd Item Number equals with "LOT SQD STOCK" OR "LOT SQD SPECIAL"

THEN Supplier ID equals "SQD"

ELSE Supplier ID

END

END

Marc_H

Hi, Swagner,

 

Hopefully this is of use for you.

 

- Filter into two tables based on the criteria you mentioned below.

- Add Constant 'SQD' or whatever constant you're going to replace your Supplier ID with. (You have to do it for both tables so you can append at the end.)

- Set column value from your Constant column to overwrite your Supplier ID column for 'LOT SQD STOCK|SPECIAL' table.

- Append.

 

Let me know if you have any questions.

 

Best wishes,

 

 

 

Marc Ha

 

P.S.: You can add in the Select Column function and choose only the columns you need since this method will give you two extra columns from the constanct columns - SQD and Not SQD.

 

- 

BlueRooster

I'm not sure if you can do that via a Magic ETL, but I know you can with a SQL transform. It would just be a case statement like the following:

 

SELECT *, CASE WHEN `2nd Item Number` LIKE '%LOT SQD STOCK%' OR `2nd Item Number` LIKE '%LOT SQD SPECIAL%' THEN 'SQD' ELSE `Supplier ID` END  AS 'New Supplier ID'

FROM table

 

Hope that helps. Let me know if you have any questions.

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

Marc_H

Hi, Swagner,

 

Hopefully this is of use for you.

 

- Filter into two tables based on the criteria you mentioned below.

- Add Constant 'SQD' or whatever constant you're going to replace your Supplier ID with. (You have to do it for both tables so you can append at the end.)

- Set column value from your Constant column to overwrite your Supplier ID column for 'LOT SQD STOCK|SPECIAL' table.

- Append.

 

Let me know if you have any questions.

 

Best wishes,

 

 

 

Marc Ha

 

P.S.: You can add in the Select Column function and choose only the columns you need since this method will give you two extra columns from the constanct columns - SQD and Not SQD.

 

- 

ST_Superman

Nice job.  You beat me to it ?