Magic ETL Joins - include table name as prefix

NateBI
NateBI Member
edited January 2023 in Charting

Hi All,

When joining tables in the MAGIC ETL, how do I include the table name as a prefix for the columns?

Sometimes the 'Select Columns' transformation offers a rename suggestion in the format, 'TableName.ColumnName' but it is not always suggested and requires using the 'Select Columns' transformation before each join.

Currently renaming columns but surely there's a better way:


image.png


Let me know!


Kind regards,

Best Answers

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    On the join tile, under step 3, you can click the + sign to make as many alterations as you want on column names by choose the rename option. It defaults to the name of the tile before it followed by a period and the column name. You can edit it to however you want.


    image.png


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • NateBI
    NateBI Member

    Hi @MarkSnodgrass unfortunately, when the columns have 'no conflictions found' it doesn't include the table name as a prefix by default (see the rename input box empty):


    image.png

    I'm looking for the option to add a prefix for conflicting and not conflicting columns.

  • MarkSnodgrass
    MarkSnodgrass Coach

    @NateBI even if there are no conflicts, in the Rename To box, you can type whatever you want, so you can type in the tablename.columnname for each column. It is manual, but that is how you would accomplish what you want.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • NateBI
    NateBI Member
    edited January 2023

    @MarkSnodgrass Yep I understand, this is essentially what I've achieved with 'Select columns'. When there are 40+ columns per table and multiple tables being joined, having to rename each column makes for a slow day. It may be one for the feature suggestion then!

  • Domomon
    Domomon Member

    This capability is available in Power BI, and I've been missing it a lot. Completely agree with you @NateBI.

  • NateBI
    NateBI Member
    Answer ✓

    @Domomon Yep I've added this as an idea here, give it a vote if you still want!

    https://dojo.domo.com/main/discussion/57659/option-to-add-prefix-in-magic-etl-joins/p1?new=1

  • NateBI
    NateBI Member
    Answer ✓

    @MarkSnodgrass a solid workaround:

    1) Join the table once
    2) Join the same table again

    3) 'Fix' all dupe named columns to the right/left - this auto generates the prefix as we know
    4) Remove the first join

    The 2nd join widget keeps all the prefix columns.
    Hope this helps for the time being folks!

Categories