Aggregate and ETL question

A2024
A2024 Member
in Magic ETL

Hello all, I have the below workflow in my Magic ETL. Just posting a sample version below.

My question is - I have a ProductID that I join from both the data sources and then do a group by and output to a file.

But I also need to know Total count of Product ID or row count from TEST spreadsheet source. Is there a way I capture the Total count and get that to the same output ?

I can do this in a separate card and display in dashboard. But I want to get that in the same workflow that I am using for my main dashboard.

Picture1.png

Please help

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    If you can have all of the records from your top dataset in your output dataset then you can do a left join on your dataset instead of an inner join. Then in your card do a count of the id file from your TEST dataset to get the total records that match

    if you just want the entire count then I’d recommend running your TEST dataset through a group by tile and count in there and output it to a separate dataset

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    If you can have all of the records from your top dataset in your output dataset then you can do a left join on your dataset instead of an inner join. Then in your card do a count of the id file from your TEST dataset to get the total records that match

    if you just want the entire count then I’d recommend running your TEST dataset through a group by tile and count in there and output it to a separate dataset

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • A2024
    A2024 Member

    Thanks @GrantSmith for your quick response. I will need to do a matching on both the sources to get just the matched records. So I will need an inner join.

    Yes Thanks @GrantSmith . I will add a Group tile and output to a different dataset.

    The only issue I have with that approach is, in the dashboard I want to see both the above outputs together in a tabular form or pivot chart. Do you think this will be doable?

Categories