Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
You can define the dimension in the table as:
SubField(CSD,';',1)
And the measure as:
Count(DISTINCT SubField(CSD,';',1)
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)