Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI Abhishek,
Distinct function could be helpful.
Can you post an example?
thanks,
Rajesh Vaswani
Hello,
Did you tried to use DISTINCT keyword. like only(DISTINCT email_address)
Thanks,
ASINGH
Hi,
did you try max function in your table?
Ariel
I am new to QlikView so dont have much information how to do same.
Hi Abhishek,
You cold also explore maxstring, firstsorted value if its relevant to your context.
thanks,
Rajesh Vaswani
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
Hi,
Try to put max(Phone_Number) in the expression
BR
Ariel
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
Try to load your data with a WHERE clause:
LOAD
Contact_Name,
Email_Address,
Phone_Number
FROM .... WHERE not EXISTS(Email_Address);