How to transform from Excel Sumif formula to Domo ?

user04285
user04285 Member
in Magic ETL

Hi, I have a data report with a template that looks like this in Excel:

Comb.png

And I need to transform this into a card. 

So far I managed to make beast mode like this: 

Sum( CASE WHEN CONCAT( product_code, "_", account_id,
      account_name) = account 
THEN Value ELSE 0 END)
AS 'Cost'

But how do I take into account that the value for the Product Code is not fixed? And also, is making a card that looks like this template possible in Domo? Thanks a lot!

Comments

  • Valiant
    Valiant Coach

    Can you provide an example of the dataset in Domo? Will make coding the final result easier if I know what you're data looks like inside Domo. And you're definitely on the right path.

     

    As far as if it's possible, yes it is. What you're going to need to do is basically create the card as you want it in a dataflow (doing the calculations there as well). Then you can just use a  table card to display it. 

    I should be able to help on this front once I know what youre datasource looks like here as well.

     

    Sincerely,
    Valiant

  • user04285
    user04285 Member

    Hi @Valiant , thanks for replying. 

    The datasource is this Excel file. So basically what we are trying to do is to replicate the data from Excel to Domo. In this case, the data file will be the second tabel on the right and another mapping table that lists:

    City, category, sub-category, product code (that we can map manually and upload to Domo).

     

    Can you help with this? 

     

    Thanks!

  • rado98
    rado98 Contributor

    I would start by breaking up the Account name into "Product Code" and "Fee Type", do this in either Excel or WB.

    After that join in ETL using the Product Code of that table to the Mapping Table.

    From there it should be quite easy to generate that output table. I think Sumo might work here or a transposed version of the table you have in the stadard type table card.

Categories