In the CostTable if the costprice changes a new record is added with a valid from date. So each CostPrice for a Costtype is valid from its validfrom date to the day before the valid from date of a new CostPrice.
In my example for Costtype A we have two records
Valid from 01.01.2015 - 95
Valid from 01.01.2014 - 88
So if a Document from the DocumentTable contains 'Costtype A' and a Creation Date >= 01.01.2015 the CostAmmount of 95 must be linked, if the creation date is >=01.01.2014 and <= 31.12.2014 the CostAmmount of 88 must be linked.
first off all, thank you very much for your effort. The results are correct. But what I don't like is, that the document records get multiplied by the costtype records. Because where I want to use that, there are large amounts of records in the DocumentTable as well as in the CostTable.
Isn't there a possibility to include a statement within the LOAD Statement for the DocumentTable to lookup the first record in the CostTable where the Costtype matches the Costtype of the current document line and the validfromDate is less equals DocumentCreationDate, and write the Cost-amount into a field of the documentTable. In that case I only had to sort the CostTable by ValidFromDate descending.
I don't know if you can do it without a load, but if you will look for each record of document table the value at cost table, i think to do it you ll need 2 "for" functions and its slower then left join and residente load with order by.
I just made another transformation at table and here it is
thank you. I'll give it a try. But in my document table there are ~1.300.000 records and in the costtable for each costtype at this moment 6 different date records, so that would result in ~ 7.800.000 in the resulttable before you begin to drop the records with no "flag_correct_value_row = 1".