Partition in Beastmode

Options
Jbrorby
Jbrorby Member
in Magic ETL

I'm hoping someone may be able to help me understand what I am doing wrong with my beastmode logic, or if what I am trying to do is just not possible in beast mode.

 

I have done rank and windows in ETL before, but I am trying to avoid creating a separate dataset.

 

I'm wanting to filter my card to locations, by day, that had 10 or less registered donations.  Each donation is given a completely unique number called a donation id.  By necessity, there are multiple rows with the same donation id in our data.   So, normally when I want a count of donations, so as not to double count the same value, I just use the count (distinct donation id) beast mode.   But, since I am wanting to narrow down to locations with 10 or less donations ids, and since you cannot put the distinct count in a filter, I was hoping to accomplish this with a type of rank/partition concept.  I've had success with this type of scenario using Rank/Window in ETL in the past, but I was hoping to accomplish this same concept in a beastmode. I know that the Partition in the beastmode is not supported by DOMO, so maybe this isnt possible.

 

Here is my beastmode:

rank( `lte_donation_general.donation_id`) over (partition by `lte_donation_general.collection_date`,`lte_tbl_calendar.location_name_tzw`,`lte_donation_general.donation_id`)

 

What it is doing for me, though, is that it is counting the occurences of each donation id per date/location combination

 

I have attached an example

 

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    Hi @HashBrowns 

     

    Try this (I don't have your exact data set to test so may not exactly work):

    SUM(COUNT(DISTINCT `lte_donation_general.donation_id`)) over (partition by `lte_donation_general.collection_date`,`lte_tbl_calendar.location_name_tzw`)

     

    You're essentially trying to calculate the number of unique donations for each date and location. 

     

    Window functions / partitions are supported in Domo it just requires a feature switch. Talk to your CSM.

     

    In terms of filtering you won't be able to because by default Domo doesn't support filtering based on aggregate functions (i.e. the COUNT in your beast mode). You could talk with your CSM about an alpha feature you might be able to get turned on in your instance to filter aggregate functions but again, it's an alpha feature and may not always work as intended.

     

    If you don't want to go that route you could always pre-aggregate your data via an ETL and then filter the counts on the cards after processing.

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

Answers

  • Jbrorby
    Jbrorby Member
    Options

    sorry, I provided the wrong beastmode.  count is used in replace of rank, the first word in my beastmode above

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    Hi @HashBrowns 

     

    Try this (I don't have your exact data set to test so may not exactly work):

    SUM(COUNT(DISTINCT `lte_donation_general.donation_id`)) over (partition by `lte_donation_general.collection_date`,`lte_tbl_calendar.location_name_tzw`)

     

    You're essentially trying to calculate the number of unique donations for each date and location. 

     

    Window functions / partitions are supported in Domo it just requires a feature switch. Talk to your CSM.

     

    In terms of filtering you won't be able to because by default Domo doesn't support filtering based on aggregate functions (i.e. the COUNT in your beast mode). You could talk with your CSM about an alpha feature you might be able to get turned on in your instance to filter aggregate functions but again, it's an alpha feature and may not always work as intended.

     

    If you don't want to go that route you could always pre-aggregate your data via an ETL and then filter the counts on the cards after processing.

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

Categories