Magic ETL Help - Overwrite value in a column if another column equals one of two values
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
Best Answer
-
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.
-
4
Answers
-
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.1 -
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.
-
4 -
Nice job. You beat me to it ?
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive