Filters - Expand use of In clause

This feature is useful anywhere a filter configuration is offered. In Magic ETL, it is technically achievable via an "any" filter block with an equals check for each value. There, this would be a convenient improvement to the user experience.

 

It seems that card/analyzer filters support arbitrary exclusion/inclusion based on a set of values for TEXT fields. It would be great to provide this option for integer fields, since we often use integers as immutable status codes for categorizing data. I guess we could provide a DataSet mapping to a string label and use that. I could see that being very intuitive and user-friendly for an ad-hoc analyzer filter, but pre-baked filters in cards would require changing in the future if the label is altered.

 

I suppose this could be useful for Dates too if they were understood to be a Date rather than a timestamp.

1
1 votes

· Last Updated

Comments

  • I'll second this one.  I have a two-step query dealing with Sales Quotes.  First, identify all quotes that contain one or more SKU's.  Then the second step is to return the full quotation, including everyone on the quote.  

     

    Since a quote can contain more than just the SKU's in my list, I have to do it in two steps.  Sure, I could do this in SQL but how is it done in Magic?  

     

    I'm doing this to analyze product drag and see what sells along with the SKUs I'm interested in.  

     

    --- get list of Quotes
    CREATE TEMPORARY TABLE VMAX_quotes AS (
    SELECT DISTINCT master_quote_number
    FROM w_quote_line_d
    WHERE w_quote_line_d.item_description IN ( '%SKU_1%', '%SKU_2%', '%SKU_3%', ...'%SKU_n%', );


    --- step 2: build the full quotes based on the master quote numbers above.
    (
    SELECT *
    FROM VMAX_quotes VMAX

    INNER JOIN w_quote_f f
    ON (VMAX.master_quote_number = f.master_quote_number)

    INNER JOIN w_quote_line_d d
    ON (f.quote_number_guid = d.quote_number_guid AND f.quote_line_number_guid = d.quote_line_number_guid)

    ORDER by f.master_quote_number, f.quote_version_number
    )
    LIMIT 5000;

    I don't what to have to type in 55 SKU's into the filtering box.  I haven't figured out how to use a table to do this quite either.  

     

    Suggestions?  

     

     

  • @mcoblentz You can add a dataset (webform, excel, whatever) that has all of those SKUs and upload it to your datacenter.  Then you can use it in a join in Magic.  

    That wouldn't give you on-the-fly list adjustment in the card builder, but it would work well if you don't need to make adjustments to that list very often. It's a thought.

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • I will give it a shot - I have to rebuild the dataset due to some missing fields.  I'll post some screen grabs as I progress.  I don't think it will be very pretty, but since these tables are 30M rows each, I'd rather do it in Domo and ETL than in SQL.  

  • Not the simplest flow but I think this may work.  

     

    2016-09-08_15-53-24.png

    o

  • Nice.  Great use of the transform description field.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Oops.  That did not turn out as expected.  

     

    2016-09-09_11-22-52.png

  • That's a bug.  Switch around the inputs on the join where that error happened.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thanks for submitting this @cr1ckt and the additional discussion @mcoblentz and @AS.  I'm assigning to our product manager @ckwright for review.

  • @cr1ckt Thanks for this information and detail. This is helpful, we will review. 

This discussion has been closed.