I have an application that loads monthly client sales of our products. There is a mapping table with the correspondance client_itemcode --> internal_itemcode.
The invoices are loaded and mapped to the internal_code to then link to our product hierarchy and to compare with our out-going sales.
Recently the team informed me that the client has changed a few items. Example they are now purchasing our internal_itemcode Y and selling it as client_itemcode A, where before our internal_itemcode X was sold as client_itemcode A.
For historic views, we should keep the old mapping. So my question is what is the best way to add a date as a condition of the mapping ? I imagine the date field in the mapping to be the first date of that mapping combination.
Currently my mapping commande in the invoices load script looks like :
text(Applymap('Correspondance_LTR_to_VIF', client_itemcode, null())) as %ARTICLE_ID,
this is a tricky problem. If you added a date field into your mapping table, you'd have to repeat the same item codes for every single date in the past and for every single date in the future (e.g. after the change). Plus, the changes could continue in the future...
I'd introduce another code, like "Version", and calculate a secondary mapping table that would be built using IF() conditions, with conditions like "For Item A and dates before X, use Version 1, for Item A and dates after X, use version 2. Then, the main mapping table could be enhanced with the "Version" field - for example, the first field could be concatenated between the Client Item Code and the Version field.
I forgot that a mapping table have only two columns!
I like the idea of your solution, however it limits the number of versions to the number of secondary tables I want to write in. Potentially, the code could change multiple time through the years. Right now, I only have one change for a handful of codes, but there's no reason that codes could not change again in the next financial year, etc.
Maybe a mapping load is not the best solution. My problem rests in the version date field for the correspondance between the client and internal codes. Maybe I need to do some sort of join between the two tables (invoices and client/internal code correspondance) that would allow me to do a conditional join on the date version compared to the invoice date.
I think that you could define your code changes in such a way that won't require constant changes in the script. On the other hand, using multiple JOIN statements with WHERE conditions would actually require to add another join every time there is a change in codes.
