Beast mode for count distinct by concatenating two columns

ozarkram
ozarkram
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

    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

    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
    edited May 2022

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

  • mhouston
    mhouston Contributor

    @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

    @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