Magic ELT Data Flow Unexpected Results

Boudrel1
Boudrel1 Member
in Magic ETL

Hello,

 

I have two input data set that were trasnformed in SQL.  I have two columns in each data set.  Column 1 is the part name, this column is the exact same in each data set, for example: row 1, in data set 1 & 2, contans the exact same text.  The ladder is true for each row in each data set.  The second column is the differing column and the each contain whole numbers.  I am trying to join the two data sets together, then do a addition calculation to het the SUM of the two columns.  The problem comes in at the JOIN, when I join the two data sets, the result is the fist row of data for each data set being repeated 100 times.

 

Data Set 1:                                                                                                                                          

 

data_set_1.png

 

Data Set 2:

data_set_2.png 

Join Result:

 

data_set_1-2_join_results.png

 

As you can see the Join Result is name 101CM repeated.  It did not join my data sets together.

 

What am I doing wrong?

 

Thanks

Best Answer

  • Godiepi
    Godiepi Coach
    Answer ✓

    Hi,

     You might want to try this and make sure the name columns have unique values, otherwise the results will repeat as many times as there are possible combinations

     

    Screen Shot 01-12-18 at 11.36 AM.PNG

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Answers

  • guitarhero23
    guitarhero23 Contributor

    Can you post a screenshot of the ETL you currently have setup?

     

    Are each of the values in column1 unique? Sounds like it, just checking


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Boudrel1
    Boudrel1 Member

    OK, here are some screen shots of the ETL Data Flow.

    Here I set up the Data FlowHere I set up the Data FlowThe data in height_ft_in_convert is correctThe data in height_ft_in_convert is correctThe data in Set Column Type 1 is correctThe data in Set Column Type 1 is correctHere is the config. it should combine the data from from each data set if the identifying columns have the same value.Here is the config. it should combine the data from from each data set if the identifying columns have the same value.The result is the first line of data, which is correct, repeated 100 times.The result is the first line of data, which is correct, repeated 100 times.

  • Godiepi
    Godiepi Coach
    Answer ✓

    Hi,

     You might want to try this and make sure the name columns have unique values, otherwise the results will repeat as many times as there are possible combinations

     

    Screen Shot 01-12-18 at 11.36 AM.PNG

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • guitarhero23
    guitarhero23 Contributor

    Sniped. My response I was typing.

     

    A couple more questions I was preparing:

    • What exactly is your set column type ETL doing? Is the data not coming as a number?
    • Is there a reason you have an input dataset in their that doesn't connect to anything?

     

    It might be due to the JOIN type you are doing. The full thing being greyed in is a full outer join which is giving you everything, might want to try an inner or left outer depending on your data.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • guitarhero23
    guitarhero23 Contributor

    Actually I'm doing a test with your data and getting the same thing, even with an inner join. Hopefully I can followup shortly.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Boudrel1
    Boudrel1 Member

    Im gona test it out, but i think the problem is there is not a unique idenifer for each row.  For example an ID column.  Similar to a column that counts the rows.  I can then join the data per the counting column and not the name column.

  • guitarhero23
    guitarhero23 Contributor

    The good news here is that your data looks like it's correct, it's just that the JOIN doesn't remove the column from showing up in this case.

     

    If you can do it via a MySQL dataflow you can choose to just not select that field and it won't show. If you want me to walk you through it I can.

     

    Dojo 1.PNG


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Boudrel1
    Boudrel1 Member

    Ok,  I am trying to give my table a new column for an unique identifier.  I am wanting to ALTER TABLE table_name ADD column_name column_type FIRST;

     

    I keep getting a Syntax error.  If I dont't use the SELECT command, the error tells me it can not gernertate an output table, and no matter where in the syntax I put the ALTER command I get a syntax error.  

     

    How can I add a column to an existing table in domo?

     

    Trying to add a column to this existing table.Trying to add a column to this existing table.

  • guitarhero23
    guitarhero23 Contributor

    Do you definitely need a rowID as opposed to just not showing one of the duplicates like I mentioned? I onyl say that because it seems to solve the problem but I don't know the answer to your most recent questions so you'll have to see if someone else knows.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Boudrel1
    Boudrel1 Member

    Yes I need an ID column.  WHile what you did helps it still repeated the first valuse for 100 lines.  I need the unique ID so I can matche and add columns per the ID number not the "name" column, because I can have the same value in the "name" column.  Thanks for your help.

  • guitarhero23
    guitarhero23 Contributor

    The inner join worked? What did you change?


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**

Categories