ETL and Data Integration tools - Modern ETL software for everyone
|
Welcome,
Guest
|
|
TOPIC: Tagging Duplicates with Parent ID
Tagging Duplicates with Parent ID 1 year ago #2699
|
|
|
Re: Tagging Duplicates with Parent ID 1 year ago #2700
|
|
|
Re: Tagging Duplicates with Parent ID 1 year ago #2701
|
I am actually creating 2 separate files. The first file will be used to create the parent record with the highest customer record number[External Unique ID] for each [Household Name].It is the first field,left of the decimal.No duplicate records allowed for Parent record.No duplicates allowed for 2nd field [Household Name]in the Parent file.This record is created from PK1 Sort-Group-Validate functions "[External Unique ID]","[Household External ID]","[Household Name]","[Store External ID]","[Main Phone #]" "5372.15501","5372.15501","ANALO_608_GLENGARY_DR_15501","5372.15501","Pittsburgh","4129636615" The 2nd file will be used to join the same household records to the Parent record (Including the Parent record) using the first field in the parent X-Referenced to the 2nd field [Household External ID] in the file containing all records. So in the 2nd file all of these records would be Opportunities Belonging to the same Parental Household Record with the 1st record creating the Parent Even though the 2nd file contains the specific Contract information for each opportunity in the Household. "5372.15501","5372.15501","ANALO_608_GLENGARY_DR_15501","5372.15501","Pittsburgh","4129636615" "5326.15501","5372.15501","ANALO_608_GLENGARY_DR_15501","5372.15501","Pittsburgh","4129636615" "5253.15501","5372.15501","ANALO_608_GLENGARY_DR_15501","5372.15501","Pittsburgh","4129636615" The 2nd file actually looks like this and uses the [External Unique ID] field to separate it as a unique opportunity (Contract) but is uploaded to the Household record utilizing the [Household External ID]created from the first pass through the tool. "[External Unique ID]","[Household External ID]","[Contract Date]","[Closed Revenue]","[Store EUID]","[Opportunity Name]","[Sales Stage]","[Description]" "5372.15501","5372.15501","2010-03-11 00:00:00","2060","Pittsburgh","ANALO_608_GLENGARY_DR_15501"","Closed/Won","Double Hung Window,2,344,Pcust,(Cash), 2 DH, Dep 3/11;,216-226-5018, (Cash). 2 DH;" "5326.15501","5372.15501","2004-12-06 00:00:00","2060","Pittsburgh","ANALO_608_GLENGARY_DR_15501",","Closed/Won","Replacement Window,3,112,SelfGen,3-DH Tan came in as wht should be tan" "5253.15501","5372.15501","2004-10-09 00:00:00","3972","Pittsburgh","ANALO_608_GLENGARY_DR_15501",","Closed/Won","Replacement Window,3,112,ROP7/31/04,jim to finish coil 12/23; jim to finish coil work; seth to do caps 1/19,3-4Lite CS Wht; install 12/14" As A side note it would be great if I could accomplish this in one pass!  I was just approved to use an address scrubbing service so the target file will be in Excel & Final final file will be .CSV |
|
|
Re: Tagging Duplicates with Parent ID 1 year ago #2702
|
David
I have created for you a working example based on the data provided by you before The only difference that I am using calculation + keep value function Peter
Attachments:
|
|
|
Re: Tagging Duplicates with Parent ID 1 year ago #2704
|
This is not working correctly when I try to add it before my existing sort for duplicate records
 . I will send you copies of my files so you can see where I am on this piece.Attachment To_DBS.zip not found |
|
|
Re: Tagging Duplicates with Parent ID 1 year ago #2705
|
There a attachment file size limit.
1 meg I believe. I have attached another example hopely this is what you wanted Peter
Attachments:
|
|
|
|
Testimonials
Our prior imports were done using VB. Our VB expert retired. No one in my group knows VB. So Visual Importer was the answer to the lack of VB skills problem my group experienced.
Ron Bradshaw,
IT Reporting Manager,
ConAgra Foods / Lamb Weston
Ron Bradshaw,
IT Reporting Manager,
ConAgra Foods / Lamb Weston
User Login
Our customers
 |
|
|
|
|
 |
