joining data in vertical and horizontal formats

Crockett
Crockett Member
in Connectors

I am trying to provide single sources of entry for loan data information that can feed all reports about that data.  Currently, the primary source for some of the loan data is entered into an Excel spreadsheet with 'property' as the primary key.  This file contains alot of other unrelated data points.  The loan data is listed horizontally in this file and some 'properties' have multiple loans.  The primary loan identifier and the data related to them are  differentiated from one another by adding a 1, 2 or 3 to the column name.  I want to join this loan data to data in another Excel spreadsheet that is the primary source for the remaining data about these loans but the key to this spreadsheet is the loan identifier, not the property, and each loan in this spreadsheet is listed on a separate vertical line.  Is there a way to flip the relevant data in the horizontal file to vertical so that it can be joined with the data in the vertical file?

Best Answer

  • Darius
    Darius Domo Employee
    Answer ✓

    Hello, Crockett,

     

    You can pivot data with the functionality of our Dataflow tools. The easiest approach to pivoting your horizontal data in preparation for joining it to the other loan data is through Magic ETL. Here is a good resource to refer to on this topic (under the "Collapse Columns" heading):

     

    https://knowledge.domo.com?cid=etlactionseditcolumns

     

    Let us know if you have follow up questions!

     

    Regards,


    Darius Rose
    **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"

Answers

  • Darius
    Darius Domo Employee
    Answer ✓

    Hello, Crockett,

     

    You can pivot data with the functionality of our Dataflow tools. The easiest approach to pivoting your horizontal data in preparation for joining it to the other loan data is through Magic ETL. Here is a good resource to refer to on this topic (under the "Collapse Columns" heading):

     

    https://knowledge.domo.com?cid=etlactionseditcolumns

     

    Let us know if you have follow up questions!

     

    Regards,


    Darius Rose
    **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"
  • Crockett
    Crockett Member

    Thank you!  I actually had looked at that resource earlier but I am having trouble applying it in this situation because of the multiple columns I need to collapse for each loan.  Is there another resource available that deals with more complex situations?

  • Crockett
    Crockett Member

    Thanks for responding.  I am going to accept this solution because I was actually able to join the data without having to collapse it!  

Categories