Delta Data loads - Aperture v2.0

Hi

this is a follow on from the delta data loads for version 2.0.

Not sure if a better way in v2.0.12 but l would like to do the following:


The previous answer explains if a null field and now field is populated. Is there an easier way if you get the full record if a change

so if you had in original file - customer id is 111

111 Sue Smith 3 smith street Moe vic 3220 01/01/1970

New line comes in for customer 111

111 Sue Smith 22 Smith street Balwyn vic 3103 01/01/70

I want to find the record in original file based on customer id and replace the entire record is this possible without going through each column

if customer can not be found in file, consider a new record and add to database

Then if status on line is inactive l can do a step after to remove

Please advise

thanks

Answers

  • Clinton JonesClinton Jones Experian Elite

    The only way I can think that you might do this, @Carolyn is to take the entire record and hash it and use the hash as a uniqueID of some sort

    Every time the data is read in it would be hashed to a new hash column.

    If something changes, then the hash would be different, even if the record ID is the same

    You'd still have to work out what had changed but at least you would know which records to look at .

    Does that help?


  • Sueann SeeSueann See Administrator

    @Carolyn to illustrate what @Clinton Jones meant, see this example:


  • Chris DownerChris Downer Administrator
    edited July 31

    @Carolyn I had a stab at this - the below workflow takes the delta (does a group in case there is more than one new record in the delta) and joins it to the original data. We then perform a function to retain the information from the original record if the data field is blank but if there data field is populated and is different from the original value it will replace it (thus you get a more complete record rather than just replacing the record wholesale)

    Function:


    Workflow:


    It's a bit long-winded so i attach the workflow below as a .dmx and the dummy data so you can import it and have a look.

    The results


    Hope this helps!

  • CarolynCarolyn Contributor

    Hi Chris

    Thank you l will give this ago tonight and see how l go.

    Let you know

  • Chris DownerChris Downer Administrator

    Hi Caroline

    It occurred to me that if you just want to keep the most recent record (in a scenario where your records in the delta come in sequence). You can just Union the original and the Delta and then add a Group step and aggegate - last value. A bit simpler if that is what you are after:


    Or you can use a Union and then the Harmonize step - in the example i provide you need to use total length rather than most complete because i added a dud record. But you can use most complete in your scenario or create your own score based system -

    just some other options for you to consider!

Sign In or Register to comment.