How to map 1 value into the same row of another column in ETL

user09909
user09909 Member
in Magic ETL

Please see attachment. I need to map a value from one column to another. 

 

Context: Marketing's Department ID needs to be the same as its Organization ID. I've tried Value Mapper but it seems like that will only allow me to make a brand new column. I don't want a brand new column.

 

Please help! Which ETL function should I use?

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Is the Marketing Department the only department that is missing an Organization ID?

     

    if all the other rows apart from marketing have a value in them, you could just use a replace text to replace any null or blank values with 4410.

     

    otherwise you would need to filter just the marketing values first and then perform the replace text on the organization I’d field. 


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Is the Marketing Department the only department that is missing an Organization ID?

     

    if all the other rows apart from marketing have a value in them, you could just use a replace text to replace any null or blank values with 4410.

     

    otherwise you would need to filter just the marketing values first and then perform the replace text on the organization I’d field. 


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user09909
    user09909 Member

    Thank you! I ended up doing separate filters for each department that was missing an Organization ID and then using Value Mapper to populate the blank fields. I then appended the rows back together.

     

    I did not use Replace Text because it does not work on numbers.

     

    Thanks!!

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I would encourage you to change the field type of any ID field to a string. Only use values for fields that you want to do calculations on


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • EF1218
    EF1218 Member

    That was such a good trick to filter the rows and re-append! I just had a similar issue and that solved it. Thanks!

Categories