Need Ideas/Help for Creating a Card

SLam
SLam Member
in Magic ETL

There are orders (non-unique) that can have multiple services assigned to them and can also have different vendors assigned. Trying to identify a way in Domo to weed out some orders that fit these criteria:

  1. If an order has 2 or more services under the same order number and if the order has at least one service code ‘GB’
  2. Order must have had at least 2 or more different vendors assigned to it

Example: (Services will have an alias like Service.. while GB is explicitly used)

Order 123456 has three different services under it. Service1 assigned to Vendor1, Service2 assigned to Vendor2, GB assigned to Vendor2 (We want to display this order)

Order 654321 has three different services under it. Service1 assigned to Vendor1, Service2 assigned to Vendor1, GB assigned to Vendor1 (We don’t want to display this order because there was only 1 vendor involved in this order)

Order 789 has three different services under it. Service1 assigned to Vendor1, Service2 assigned to Vendor2, Service3 assigned to Vendor3. (We don’t want to display this order because there was no ‘GB’ service in this order)

Is this possible to achieve with a beast mode? I am trying to think of a way to tackle, but can't see picture how it'll work.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user077529

    How is your raw data formatted? Do you have a single column for the service type and multiple records for each order or are the different services separate column values and you have one row for each order?

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

    Hey Grant!

    These are some columns: OrderNumber, Vendor, Service all separate columns. There can be multiple order numbers with different types of services that could be assigned to more than 1 vendor.

  • GrantSmith
    GrantSmith Coach

    If you only show the order number and not the vendor or the service codes (making sure only one row per order is being shown) you could utilize some aggregates to a filter to work and then have the user click on the order number to go down a drill path to see the order details - alternatively you'd need to do your grouping and filtering in an ETL to then only display all order records which meet your criteria)

    CASE WHEN MAX(CASE WHEN `Service` = 'GB' THEN 1 ELSE 0 END) = 1 AND COUNT(DISTINCT `Service`) >= 2 AND COUNT(DISTINCT `Vendor`) >= 2 THEN 'Include' ELSE 'Exclude' END

    Again though, Order ID must be on your chart but you can't have Vendor or Service included in your chart otherwise the grouping won't work correctly.

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

    Hey Grant,

    Thank you for the idea. I tried this out but when I added the beast mode to the filter, there is only an option for 'Exclude.' I think it is getting confused where in the 'Include' section.

    Eg.jpg

    Attached is a screenshot of what the data looks like. I only have the Order Number in the column.

Categories