Updating data in a dataset based on date

tjlew903

Hi Everyone. 

 

Long time lurker first time poster (please be gentle)

 

I have a dataset that contains a lot of customer data (not to brag or anything) and I'm trying to update the customerID column for one specific customer from 123 to 456 only when another column "event date and time" is before 8/1/18. Essentially, I want all data for customer 123 to seem like it was for 456 for events that happened before 8/1/18.  

In SQL I’d simply say: update TABLE set CustomerID = '456' where "CustomerId" = '123' and "Event Date and Time" < '8/1/2018 %’ but I’m not sure the best way to do it in Domo. ETL? Redshift? 

 

I'm probably missing something obvious. Any help is greatly appreciated.

Property_Ninja

Hi tjlew,

 

I believe the best way to do this would be in either a MySQL or Redshift flow. You can simply do something like

 

select 
column1,
column2,
column3,
`event date and time`,
CASE WHEN `event date and time` < '2018-08-01' and customerID = 123 then 456 else customerID end as customerID
from customertable

 

Possibly a slightly more techincal approach would be to write a stored procedure in a MySQL dataflow.

CREATE PROCEDURE UPDATE_TABLE()

UPDATE `customertable` SET `customerID` = '456' WHERE `customerID` = 123 and `event date and time` < '2018-08-01';

END

-- Then create a new step that has "CALL UPDATE_TABLE" to actually run the command

 

Hope this helps,

 

Brian

 

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

Property_Ninja

Hi tjlew,

 

I believe the best way to do this would be in either a MySQL or Redshift flow. You can simply do something like

 

select 
column1,
column2,
column3,
`event date and time`,
CASE WHEN `event date and time` < '2018-08-01' and customerID = 123 then 456 else customerID end as customerID
from customertable

 

Possibly a slightly more techincal approach would be to write a stored procedure in a MySQL dataflow.

CREATE PROCEDURE UPDATE_TABLE()

UPDATE `customertable` SET `customerID` = '456' WHERE `customerID` = 123 and `event date and time` < '2018-08-01';

END

-- Then create a new step that has "CALL UPDATE_TABLE" to actually run the command

 

Hope this helps,

 

Brian

 

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

AS

Brian makes good points.  I'll follow that up with a comment.  You won't really be able to dataflow your way into updating the original data in Domo (not that I'm aware of, anyway).  The options presented will lead you to a second, improved version of the original data and both will reside in your data center.  Best would be to update the fields in your source data.  

In case that doesn't work, the secondary dataset is your easiest option.  It may be possible to update your original data but would likely require use of advanced techniques like the API.

tjlew903

Much appreciated. I was able to parse out the data I needed using your suggestion. 

 

Thanks again, 

 

Tom, MajorDomo @ KonicaMinolta Healthcare