Sometimes a person would Type 9999999999999999 or 0000000000 8999999998888888, ... instead of typing their identification number. I want a column to identify those cases, meaning the cases in which a single string (In this case a number string) is typed more than 7 times.
I have no idea on how to do it.. My best guess would be to count each number using len, but that would be at least 10 IF statements opened... Any suggestions?
We can find the maximum digit count as follows (as a calculated column):
MaxDigitCount =
VAR n = LEN ( [ID] )
VAR Digits =
ADDCOLUMNS ( GENERATESERIES ( 1, n ), "@D", MID ( [ID], [Value], 1 ) )
VAR Frequencies =
GROUPBY ( Digits, [@D], "@Freq", COUNTX ( CURRENTGROUP (), [Value] ) )
RETURN
MAXX ( Frequencies, [@Freq] )
Suppose [ID] = "899999999777". Then n = 12 and Digits is the table generated by creating a list from 1 to 12 and adding the column of digits corresponding to each of those positions.
Digits =
| Value | @D |
|---|---|
| 1 | 8 |
| 2 | 9 |
| 3 | 9 |
| 4 | 9 |
| 5 | 9 |
| 6 | 9 |
| 7 | 9 |
| 8 | 9 |
| 9 | 9 |
| 10 | 7 |
| 11 | 7 |
| 12 | 7 |
Then Frequencies summarizes this table by grouping on @D and counting the number of occurrences of each distinct digit.
Frequencies =
| @D | @Freq |
|---|---|
| 8 | 1 |
| 9 | 8 |
| 7 | 3 |
Finally, return the maximum value in the @Freq column.
Using this, it's easy to check if the value is greater than 7. You can modify the final line to
IF ( MAXX ( Frequencies, [@Freq] ) > 7, ">7", "<=7" )