Load/Join data based on valid from date

[#1]

Hello,

is there anyone who can help me with the following Problem?

I have two tables

one with documents containing a Costtype and a CreationDate

DocumentTable

and one with costs per costtype depending on a valid from date

CostTable

and I would like to get the following result tables with documents with correct cost depending on the Costtype and CreadtionDate of the document

ResultTable

But I can't simply join them, because there is no excact match in both tables.

So, is there something like a conditional lookup function I can use?

regards

Michael

[alexandros17]

If The costtype is not the same in both table the only way is to normalize the fields making them equal, if you mean that dates do not match then give us the complete rule to let them match.

let us know

[#3]

I don't understand the logic to choose what field ll remain at result table but i think you can do it with something like that.

ResultTable:
Load * Inline [
Document,Key,CreationDate,Cost
];

LET rowNextDocumentTable = NoOfRows('DocumentTable');

for iDocument=0 to $(rowNextDocumentTable)
LET vDocumentTableDocument=Peek('Document',$(iDocument),'DocumentTable');
LET vDocumentTableKey=Peek('Key',$(iDocument),'DocumentTable');
LET vDocumentTableCreationDate=Peek('CreationDate',$(iDocument),'DocumentTable');

for iCost=0 to $(rowNextCostTable)
LET vCostTableKey=Peek('Key',$(iCost),'CostTable');
LET vCostTableValidFrom=Peek('Valid From',$(iCost),'CostTable');
LET vCostTableCost=Peek('Cost',$(iCost),'CostTable');

// do your logic here

Concatenate(ResultTable)
Load * Inline[

];

NEXT;

NEXT;

[#4]

Hello Alessandro,

yes, I mean that the dates do not match.

To the rule

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.

regards

Michael

[#5]

Hello Jean,

in the result table all records of the document table should remain, with the added field of the cost-amount from the CostTable based on CostType and CreationDate of the Document.

regards

Michael

[#6]

Ok,

I'm doing it, wait a bit.

[#7]

Look if this help.

[#8]

Hello Jean.

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.

regards

Michael

[#9]

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

[#10]

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

I wonder if our System can handle that.