Updating data in a dataset based on date

tjlew903
tjlew903 Member
in Magic ETL

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.

Tagged:

Best Answer

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    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.


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

Answers

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    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.


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

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • tjlew903
    tjlew903 Member

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

     

    Thanks again, 

     

    Tom, MajorDomo @ KonicaMinolta Healthcare

     

     

Categories