Magic ELT Data Flow Unexpected Results

Reply
White Belt

Magic ELT Data Flow Unexpected Results

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


Accepted Solutions
Blue Belt

Re: Magic ELT Data Flow Unexpected Results

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'

All Replies
Green Belt

Re: Magic ELT Data Flow Unexpected Results

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

White Belt

Re: Magic ELT Data Flow Unexpected Results

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

ETL Data Flow.pngHere I set up the Data FlowETL Data Flow_1.pngThe data in height_ft_in_convert is correctETL Data Flow_2.pngThe data in Set Column Type 1 is correctETL Data Flow_3.pngHere is the config. it should combine the data from from each data set if the identifying columns have the same value.ETL Data Flow_4.pngThe result is the first line of data, which is correct, repeated 100 times.

Blue Belt

Re: Magic ELT Data Flow Unexpected Results

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'
Green Belt

Re: Magic ELT Data Flow Unexpected Results

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.

Green Belt

Re: Magic ELT Data Flow Unexpected Results

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

White Belt

Re: Magic ELT Data Flow Unexpected Results

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.

Green Belt

Re: Magic ELT Data Flow Unexpected Results

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

White Belt

Re: Magic ELT Data Flow Unexpected Results

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?

 

ALTER_TABLE_incorrect.pngTrying to add a column to this existing table.

Green Belt

Re: Magic ELT Data Flow Unexpected Results

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.

Announcements
Call for Presenters! We want to hear your Domo story, submit your entry and win a free pass to Domopalooza 2019!. Click here!