Count distinct with different formats

[Anonymous]

Hi Everyone,

I want to count the unique customer that have more than 1 # Offered using expression. but some of the CSD. Formatted Number has different format.

(ie. tel:+61399389998;phone-context=national , tel:+6139938999😎 this should only be counted as 1 customer.

Data:  

CSD.Customer Formatted Number	# Offered
tel:+61399389998;phone-context=national	39
tel:+61399389998;phone-context=national	23
tel:+61399389998;phone-context=national	2
tel:+61399389998;phone-context=national	1
tel:+61399389998;phone-context=national	0
tel:+61399389998	5
tel:+61399389998	4
tel:+61399389998	1
sip:0anonymous@anonymous.invalid	654
sip:0anonymous@anonymous.invalid	216
sip:0anonymous@anonymous.invalid	77
sip:0anonymous@anonymous.invalid	29
christopher.w@yahoo.com	5

Expected Result:

Repeat Callers
tel:+61399389998	1
sip:0anonymous@anonymous.invalid	1
christopher.w@yahoo.com	1

[petter]

You can define the dimension in the table as:

SubField(CSD,';',1)

And the measure as:

Count(DISTINCT SubField(CSD,';',1)

[sunny_talwar · Accepted solution]

petter-s‌

You might have left a closing parenthesis for the measure.

Also eustisab‌, if possible perform this transformation in the script like this

LOAD [CSD.Customer Formatted Number],

     SubField([CSD.Customer Formatted Number], ';', 1) as RequiredString,

     [# Offered]

FROM ...;

Once this is done, you can just use this

Dimension

RequiredString

Expression

Count(DISTINCT RequiredString)