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 2:

Join Result:

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

Find more posts tagged with

Accepted answers

Godiepi

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

All comments

guitarhero23

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

Boudrel1

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

Here I set up the Data FlowThe data in height_ft_in_convert 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.The result is the first line of data, which is correct, repeated 100 times.

Godiepi

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

guitarhero23

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.

guitarhero23

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

Boudrel1

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

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

Boudrel1

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.

guitarhero23

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.

Boudrel1

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

The inner join worked? What did you change?