Deleting Missing Transaction Data

I have Transaction data import set up in Data Hub. The data has no ID so I use a numbered list to create a list (Transactions #) of unique ID:s by combining a few properties. I then load all of the properties into a details module with the Transactions #- list as a dimension. 

 

Data is loaded every night and it consists of transactions from the last 60 days. If a transaction is cancelled the row disappears from the import file and of course I need to make it disappear from Anaplan as well. The rows that are older than 60 days should stay intact. I did figure out a way to achieve this but can´t help to think that there could be an easier way to do this. So I would love to hear your suggestions.

Best Answer

  • DavidSmith
    Answer ✓

    Firstly really push to get a unique key in the data file.  It makes all of this so much simple and faster.  Importing with Combination of properties is so much slower than using a code, and you have no option but to import into a list property rather than a module, going against DISCO and System modules best practice

     

    But, that said, this is a recommended approach that can use used for transactions with or without a key

    1.  Given you have a 60 day cutoff, you will need to maintain a date field somewhere.  this could be imported every day, or manually input.  You can use this to flag those transactions to keep

    2. If you can, add a column to your data file that is a boolean so that all rows are set to TRUE

    3. In your transaction list or module, add a property/line item to hold this Boolean flag

    4. As the first part of the import, clear out this line item

    5. Import the data as normal including the new "imported" TRUE field

    6. You can now check for those without TRUE

    7.  Delete those rows that have FALSE in the import field, but not those over 60 days

     

    I hope that helps

    David

Answers

  • Hi Kalle,

     

    Which area are you looking to simplify?

    What pain point are you experiencing?

     

    Thanks,

    LipChean

  • @LipChean_Soh 

     

    Basically I would just like to do this as simple as possible and I´m wondering am I missing some basic Anaplan functionality that would make this process more simple. My current solution has 6 steps/actions in total.

     

    My solution was to create another Transaction list that only contains the latest load and with it, in the Details module, I can determine the rows that were included in the latest load. Then I have a formula that checks if the row is more than 60 days old and is it included in the latest load. With this I can delete the correct items. 

  • Hi @Kalle 

     

    Please refer to the steps listed by @DavidSmith , by using the Boolean, you need not create another Transaction list and another Detail module for comparison purpose.

     

    I suspect the number of actions will be the same, but it's always better to have 1 less Transaction list, and 1 less module to maintain and calculate on the Anaplan server. So all in all, your process should run faster.

     

    And as a mid term target to aim for, please refer to the first paragraph of what @DavidSmith said about getting your IT department to create the Unique Code in your data file.

     

    Thanks,

    LipChean

  • There is always an unique key in any transaction in any source systems. If they say there is none, either it is not a transaction or they simply not aware of it.

     

    All databases have a primary key for each table, transactions are stored in database tables. 

     

    Regards

    Arun

  • @DavidSmith @LipChean_Soh @ArunManickam 

     

    Thank you for the comments! Seems that that I have the right idea but could make this a bit more simple by adding key and TRUE columns to the data.

     

    - Kalle 

  • Simple is better than complex
    Complex is better than complicated
    David
  • Good stuff

    Simple is better than complex

    David