How to select distinct/Unique Values

[#1]

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

[rajeshvaswani77]

HI Abhishek,

Distinct function could be helpful.

Can you post an example?

thanks,

Rajesh Vaswani

[#3]

Hello,

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

Thanks,

ASINGH

[ariel_klien]

Hi,

did you try max function in your table?

Ariel

[#5]

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

[rajeshvaswani77]

Hi Abhishek,

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

thanks,

Rajesh Vaswani

[#7]

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.

Can anyone please help me with this?

Thanks,

Abhishek

[ariel_klien]

Hi,

Try to put  max(Phone_Number) in the expression

BR

Ariel

[#9]

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

[swuehl]

Try to load your data with a WHERE clause:

LOAD

     Contact_Name,

     Email_Address,

     Phone_Number

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