Beast mode for count distinct by concatenating two columns

Options
ozarkram
ozarkram Member
edited May 2022 in Magic ETL

Hi All,

I am trying to do the following beast mode operation:


COUNT(DISTINCT (CASE when `ord_date` < CURDATE()-1 then CONCAT(`sales_no`,`part_no`) end ))

The expectation is the distinct combination of sales_no and part_no ..But I am not getting that..Can you please help me here..



Thanks,

Arun

Tagged:

Comments

  • ozarkram
    ozarkram Member
    Options

    Hi All, Can you please help me here..The expectation is the distinct count of the combination of sales no and part_no ..But I am not getting the distinct count...can you please help me here..

  • sfiene
    sfiene Member
    Options

    I only just started using Domo not too long ago, but I have worked with dates before. Have you tried using ADDATE(ord_date, interval -1 day) instead of CURDATE() - 1. If my understanding is correct, if you use a negative number this function will subtract that number from the date field given.

    It would look like this:

    COUNT(DISTINCT (CASE when `ord_date` < ADDATE(CURDATE(), interval -1 day) then CONCAT(`sales_no`,`part_no`) end ))

  • ozarkram
    ozarkram Member
    edited May 2022
    Options

    @sfiene ...Appreciate your response...But I am not able to see any change to my output...

  • mhouston
    mhouston Contributor
    Options

    @ozarkram it would be helpful if you provided an example of your dataset and the output you are getting from your beast mode, as well as what you are expecting to see.

  • ozarkram
    ozarkram Member
    Options

    @mhouston : Sorry for not being clear..I got the answer to this here

    https://dojo.domo.com/discussion/54893/help-with-beast-mode-for-getting-the-distinct-count-by-order-number-for-this-year-and-last-year


Categories