Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

How to select distinct/Unique Values


Hi All,

I created a report where I fetched data from oracle DB, here data which is coming is having duplicate values and I dont want to restrict it at data base level, here I need your help.

Suppose I am having 3 columns, contact_Name , Email_Address , Phone_Number. Now the phone number field is having 4 unique values for single records, e.g. Name ABC is having Mobile number, Office Number , Landline Number and Fax Number. The records looks unique but at the same time Email_Addresses are getting repeated and if I dont keep filter on Phone_Number then I will have duplicate Email Addresses which I want to avoid. How can I apply unique/Distinct filter on Email_Address coulmn in QlikView so that I can get unique values for email address in this case it doesnt matter if it ignore any of the value of Phone_Number column and use any one out of Mobile number, Office Number , Landline Number and Fax Number.

Thanks in advance.

Regards,

Abhishek

10 Replies
rajeshvaswani77
Valued Contributor III

Re: How to select distinct/Unique Values

HI Abhishek,

Distinct function could be helpful.

Can you post an example?

thanks,

Rajesh Vaswani

Not applicable

Re: How to select distinct/Unique Values

Hello,

Did you tried to use DISTINCT keyword.  like only(DISTINCT email_address)

Thanks,

ASINGH

ariel_klien
Valued Contributor

Re: How to select distinct/Unique Values

Hi,

did you try max function in your table?

Ariel

Not applicable

Re: How to select distinct/Unique Values

I am new to QlikView so dont have much information how to do same.

rajeshvaswani77
Valued Contributor III

Re: How to select distinct/Unique Values

Hi Abhishek,


You cold also explore maxstring, firstsorted value if its relevant to your context.


thanks,

Rajesh Vaswani

Not applicable

Re: How to select distinct/Unique Values

Hi Below is my actual requirement, where I want to delete duplicate email address and it doesnt matter if any of the phone number get deleted I just need one phone number and remove the duplicate.

QlikView_Query.JPG.jpg

Can anyone please help me with this?

Thanks,

Abhishek

ariel_klien
Valued Contributor

Re: How to select distinct/Unique Values

Hi,

Try to put  max(Phone_Number) in the expression

BR

Ariel

Not applicable

Re: Re: How to select distinct/Unique Values

Hi Abhishek

You could use a Crosstable function within the load.  This way, instead of loading 3 fields (Name, Email, Telephone Number), you will load 6 fields (Name, Email, Mobile Number, Office Number, Landline, Fax Number).

This way the email will only be created in one record and it will create different fields for the different phone numbers.

Alternatively, use can simply use a pivot table coupled with the max functionality.  I've attached my demo qvw and source xls file.

I hope this helps.

Steve

MVP
MVP

Re: How to select distinct/Unique Values

Try to load your data with a WHERE clause:

LOAD

     Contact_Name,

     Email_Address,

     Phone_Number

FROM .... WHERE not EXISTS(Email_Address);

Community Browser