Newbie23

Newbie23

Hello, I am new to DOMO and I am learning it as I work on this project.

I am trying to combine two datasets to calculate monthly supplier performance: qty rejected divided by qty received. I have dataset A, qty rejected, with tons of data. I did some calculations and was able to chart the defects of each supplier by month, see below chart.  Each color represents one supplier

I have dataset B, with monthly qty received per supplier, see below chart

Prior to grouping the data from dataset A into months, I tried to combine the two datasets using the magic ETL and Blend (DataFusion), but was not able to. Any tips?

amehdad

Hi @Newbie23, welcome to Domo and Dojo :)

A couple of questions for to then help with next steps:

• With your two datasets, did you experience an error when combining (essentially want to understand better about what you mean about "was not able to")?
• Is it necessary to bring in all the fields from both datasets?
• What's the unique identifier common to dataset A and B?

I believe that magic ETL is the best option because you can mold the data to suit your required format and also create formulas for your monthly supplier performances in a fast and effective way.

Newbie23

Good Morning amehdad, thank you for your reply, let me respond to your questions.

1. No, I didn't get any error message when I was combining the data. I saw the new columns, but couldn't see the "qty received". I took me a while to realize that one dataset A, has daily data, and dataset B has the data in months.
2. I am filtering the data from the two datasets, I am only using 5 columns from dataset A and about 3 from dataset B, both dataset have about 40 columns each.
3. The comon identifier between the two datasets is the supplier name.

Thanks for your help.

Newbie23

Hi again,

attached are the datasets I am trying to combine. Dataset A, 4 columns and dataset B 3 columns, RVCOD and Supplier code is common between the two datasets. I can do the filtering and come up with the two cards that I showed above, but I am stuck trying to combine the two. You suggested magic ETL, how will you group dataset A, so is in the same monthly format as dataset B.

Datasets.pptx

amehdad

Hi @Newbie23, thanks for sharing the dataset columns. For dataset A, what you can do is change the format of 'RRDAT' to be a month format through adding a formula tile with a code like:

DATE_FORMAT('RRDAT'),'%b %Y')

so you get something like 'Jan 2022'. Then you can use the 'Group By' tile to aggregate your measures like 'Qty Received' by month (and also supplier code and other relevant text fields). You can also do this for dataset B so that it is in an identical monthly format before you combine datasets.

For combining, use the Join tile and select 'Inner' and then select the predominant dataset (that you want to retain fields as it is) and the matching dataset (where you want only certain fields brought in that match existing records from 1st dataset) on 'Supplier Code' (or another unique identifier).

Before creating your final output, you can create your chart formulas in the ETL (as opposed to using beast mode) for better performance.

Please let me know how you go, and if you have any more questions.

Aditya_Jain

https://dojo.domo.com/main/discussion/comment/62048#Comment_62048

Hi @amehdad I think you meant Left Outer Join instead of Inner Join. Rest the solution looks good.

Another thing, the formula is a fraction, so I think calculating it on the front end might be a better solution.

As the formula might resolve to sum(qty rejected) / sum(qty received) and trying to do it in the ETL might lead to sum of fractions which might be incorrect. Just thinking out loud, pls correct if wrong.

Thanks

amehdad

Thanks Aditya, I did mean Left Outer Join

amehdad

And if it's the case that you'd need to have filters applied, then yes using beast mode is recommended but initially posed using magic ETL for the calculation due to better processing/rendering of a dashboard page.

amehdad

Hi @Newbie23, how did you go?

Newbie23

I haven't try it, sorry, I have been busy with supplier issues, hope to get back in the grove soon. I am not a programmer so I have to go and try to write the program for what you suggested, I will get to it. I will post back soon.

Thank you for checking.