Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eustisab
New Contributor III

Count distinct with different formats

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:+61399389998) this should only be counted as 1 customer.

Data:  

CSD.Customer Formatted Number# Offered
tel:+61399389998;phone-context=national39
tel:+61399389998;phone-context=national23
tel:+61399389998;phone-context=national2
tel:+61399389998;phone-context=national1
tel:+61399389998;phone-context=national0
tel:+613993899985
tel:+613993899984
tel:+613993899981
sip:0anonymous@anonymous.invalid654
sip:0anonymous@anonymous.invalid216
sip:0anonymous@anonymous.invalid77
sip:0anonymous@anonymous.invalid29
christopher.w@yahoo.com5

Expected Result:

Repeat Callers
tel:+613993899981
sip:0anonymous@anonymous.invalid1
christopher.w@yahoo.com1
1 Solution

Accepted Solutions

Re: Count distinct with different formats

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)

2 Replies
MVP
MVP

Re: Count distinct with different formats

You can define the dimension in the table as:

SubField(CSD,';',1)

And the measure as:

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

Re: Count distinct with different formats

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)

Community Browser