Data Flow Help

damen
damen Contributor
edited October 2022 in Magic ETL

Ok this one is going to be a long one.

My company keeps track of loans on an excel sheet. We are trying to migrate that excel sheet into a dataflow onto domo. There are a lot of different data sources we pull from to create this spread sheet but I am trying to bring together and formulate an ETL from just two sources right now

Currently, I am only able to get LESS rows in Domo on my filters than I am in excel. I need them to match up

So... Right now, in Domo we have two of the tables we use to query before putting this excel sheet together. I have done a left join on the 'loan shipped' table because there will be loan IDs in the 'openAPP' table that are not included in the 'loans shipped'

image.png
image.png

(I have also tried all four types of joins and still cant get the right amount

After that, I changed some of the column types from ABC to integers.

After that (and this is the same thing we have done on the excel file) we filtered on loan_id and purchase date so that instead of using all of the historical data we are only looking at about 5 years

image.png

Next we chose the columns we needed, added string to cells that were empty (but not filtered) and combined two columns that needed their string to be in one.

Now. I have 19770 rows in my excel file and 19738 from my Domo file.

image.png


image.png

We have done a vlookup on the loan_ids that are included in our excel file against the loan_ids that show up in our domo file and each of them are included in BOTH datasets that we joined together. This makes me think that somehow I filtered them out but I have no idea where to look or how to figure out how. (each of the loan_ids in domo that we are missing include ALL of the data we are looking for from the two tables we joined them on)

Any ideas would be appreciated.

If this helps, feel free to agree, accept or awesome it!

Tagged:

Comments

  • JasonAltenburg
    JasonAltenburg Coach

    Every analysts favorite game... Spot the disparity!

    Do you have any duplicate, or blank loan_ids?

    Did you try Full Outer Join?

    Have you confirmed that your input datasets are exactly identical as the inputs for your excel file?

    Does your excel file possibly have manually entered data?

    Lastly, identify exactly which loan_ids are in the output in Domo, compare that to your Excel sheet, then if your Excel sheet has loan_ids that exist but are not in the Domo output... query your Domo source tables for those loan_ids.

Categories