Counting incorrect strings in a table

[Anonymous]

Intro: I have a number of barcodes available in my table, some of which are incorrect.
The correct barcodes generally have a string length of 13 and do not contain any alphabets.
They would generally begin with a 3 string prefix that begins with the number 7. So for e.g.
7010000000001, 7020000000001, 703000000001 are all correct barcodes.

Problem: I have some incorrect barcodes in my table/data set which do not have any of the above mentioned characteristics. for eg, 11098, Y0mc, 1, 2, 898099 etc. 

Question: I am looking for a clever way to count the number of these incorrect barcodes I have. Here is what i have tried(not sure its the cleverest way too):

count(distinct [Barcode]) - count({<Barcode={'*^701*', '*^702*', '*^703*'}>}DISTINCT Barcode)

Trying to do a count of all the barcode minus a count of barcodes that contain the prefix.
I have tried this but it doesnt seem to return the correct number. Can someone please help?

Thanks in advance

[sunny_talwar]

Can you try this

Count(DISTINCT [Barcode]) -
Count({<Barcode = {"?701*", "?702*", "?703*"}>} DISTINCT Barcode)

[Anonymous]

Hi @sunny_talwar, Unfortunately this doesnt work. It returns a count of all the barcodes i have instead of doing the subtraction of the incorrect ones. 

[sunny_talwar]

Would you be able to share a sample (mocked up data) where you can replicate your issue and share it with us so that we can see the exact issue and try out few things?

[Anonymous]

@sunny_talwar I have also tried

Count(DISTINCT [Barcode]) - Len (Barcode < '13')

To try to subtract from the total barcodes minus barcodes where the length is less than 13. still no luck. 

[sunny_talwar]

I am not sure how your data looks like... a sample would really help here