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.
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
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.
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.