Magic ETL / Domo Workbench

Hello,

 

I'm trying to create a simple enough transformation when uploading a .csv file via Domo Workbench - take a value from a certain location, copy it and create a new column filled with it.

 

e.g. (in this case Row2, Col1)

 

              Col1   Col2   Col3   Col4

Row1    aaa    aaa     aaa    aaa

Row2    bbb    aaa     aaa    aaa

Row3    aaa    aaa     aaa    aaa

Row4    aaa    aaa     aaa    aaa

 

should become:

 

              Col1   Col2   Col3   Col4   Col5

Row1    aaa    aaa     aaa    aaa    bbb

Row2    bbb    aaa     aaa    aaa    bbb

Row3    aaa    aaa     aaa    aaa    bbb

Row4    aaa    aaa     aaa    aaa    bbb

 

Is there a way to do it using any ETL tools?

 

Thanks,

.

 

 

Comments

  • Can anyone help with this request?

  • user08458,

     

    Great question! This is a multi-step process. Let’s see if we can walk through this together. Please reference the screenshot below as you proceed through these steps:

     

    • Input Dataset: Load your dataset to Magic ETL via the Input DataSet
    • Filter Rows: Use the Filter Rows function (Edit Data section) to isolate the record you would like to replicate. In this instance, this is Row 2. The action is labeled as "BBB Filter".
    • Select Columns: Use the Select Columns function (Edit Columns section) to select the pertinent fields you want to add to each record. In this instance, this is Col 1
    • Add Constant: you will need two constants:
      1. The first constant is built off of the Select Columns
        1. This constant (Constant 1) will create a new field on which we can join Constant 1 and Constant 2 from the next step. We created a field called “Join” with a value of “True”.
      2. This constant is built off of the Original dataset.
        1. Here, we create a new field (Constant 2) which we can join onto Constant 1 in the next step. In order to join, we have to have the same value. Again, we created a field called “Join” with a value of “True”.
    • From the Edit Data section select the Join Data function to join these two constants together on the new column. In this instance, the new fields called “Join”.
    • Use the Select Columns function to select the original fields and the new field that you just added.
    • Use the Output DataSet function to create your final dataset.Screen Shot 2017-02-08 at 10.38.11 AM.png