How to do a distinct count of a certain number of digits?

lauramiddleton
lauramiddleton Member
in Cards, Dashboards, Stories

I have a column labelled "Key". I am wanting to do a group count of these as otherwise I have too many rows (in table format).

The first four numbers represent a project, I am currently doing a COUNT of these, and it comes back as 1 for each as the number is unique due to the numbers after the -.

Is there a way to do a distinct COUNT on the first four numbers only so that I can count the following numbers as 4 and not 4 individual rows?

2036-310

2036-311

2036-312

2036-313

image.png

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited August 2024 Answer ✓

    Yes you can do it in a beastmode.

    LEFT(`FSJ No.`,4)

    Then bring that field into your table twice, once without modification and once as a count aggregation.

    If I solved your problem, please select "yes" above

Answers

  • art_in_sky
    art_in_sky Contributor
    edited August 2024

    @laurenmiddleton You can do magic ETL or SQL to split the column into two columns. You can then count the column using distinct first 4 numbers using group by or maybe you can pivot the distinct 4 digits if the data is not too complex to list the all the project codes and count.

    You can also create a beast mode calculation LEFT(Key, 4) to create a key column and then perform Magic ETL 'group by' on the 'key ' column and count to reduce the rows.

  • lauramiddleton
    lauramiddleton Member

    @art_in_sky is it possible without Magic ETL and just purely on CARD beast mode?

  • ColemenWilson
    ColemenWilson Coach
    edited August 2024 Answer ✓

    Yes you can do it in a beastmode.

    LEFT(`FSJ No.`,4)

    Then bring that field into your table twice, once without modification and once as a count aggregation.

    If I solved your problem, please select "yes" above

Categories