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

lauramiddleton

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

ColemenWilson

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.

Arthi Annadi

@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

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

ColemenWilson

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.