How to "left joining" using some criteria

[#1]

Hi everybody.

Can't find an answer for this.

I have an invoice table, with this fields:

Number CreationDate Article Quantity

And have a table with costs history of each article, with this fields:

Article FromDate ToDate Cost

I can't find a way to add a cost to each article in the invoice table, taking the valid cost at the moment of the creation of that invoice. This means that (CreationDate >= FromDate AND CreationDate <= ToDate), because each article can have many records in CostHistory table (no overlapping of date is allowed).

How can I do that?

Thanks in advance.

Damian.

[stabben23]

Hi Damian,

1.yes

2. yes, to get all dates between the from and to dates.

3. yes, you will now have a table with all dates, grouped by item and cost.

You will have a match on all CreationDate, whatever the date is, into your map table.

4. Applymap in the most common and effective way to merge singlefields into a bigger table.

Let give it a try, i will help if your lost.

//Staffan

[#3]

Hi Staffan, I'm trying...

This lines:

Let vMinDate = Peek('MinDate',0,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',0,'MinMaxDate') ;

Will take a MinDate from record number 0 and MaxDate from record number 0 also. Grouping by item and cost, the record number 0

may not have the actual mininum date and maximum date... I'm right? Because some items may have date ranges that others items may not have.

May be I need first

MinMaxDate:

Load

min(Date1) as MinDate,
max(Date2) as MaxDate
resident CostHistory

then

Let vMinDate = Peek('MinDate',0,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',0,'MinMaxDate') ;

Is that right?

And what about the INTERVALMATCH? I still need it?

Regards.

Damian.

[stabben23]

Hi, Try the hole script, there are maybe 10 way to solv this problem, this is one of them.

And yes IntervallMatch is needed here.

cost:

LOAD

Article as Item,
FromDate as FromDate,
ToDate as ToDate,
Cost
FROM
CostTable;

MinMaxDate:
Load
Item,
Cost,
min(FromDate) as MinDate,
max(ToDate) as MaxDate
resident cost
group by Item, Cost
order by Item asc;

Let vMinDate = Peek('MinDate',0,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Calendar:
Load recno()+$(vMinDate)-1 as Date
Autogenerate vMaxDate - vMinDate;
JOIN (MinMaxDate) INTERVALMATCH (Date) LOAD  MinDate, MaxDate
RESIDENT MinMaxDate;

drop fields MinDate,MaxDate;
NoConcatenate
Temp:
LOAD
Item & Date as Key,
Cost
Resident MinMaxDate;

map:
Mapping Load
Key,
Cost
Resident Temp;
drop table Temp;

Invoice:
LOAD
Number,
ApplyMap('map', Article & CreationDate) as Cost,
CreationDate,
Article,
Quantity
FROM
InvoiceTable
Drop Tables Calendar, cost, MinMaxDate ;

[#5]

Thank you Staffan.

Trying the script. Have a problema with the sentence

JOIN (MinMaxDate) INTERVALMATCH (Date) LOAD  MinDate, MaxDate
RESIDENT MinMaxDate;

When the record count for MinMaxDate arrives to 6869841

gives me the error: OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocating 2 MB

Seems like I need a more powerfull PC...

[stabben23]

Hi Damian,

Maybe there is some error in your FromDate ToDate, feels like you havent got a ToDate for some item.

Can you send that file?

[#7]

Yes, here it is, compressed.

You must look at the table ArticulosFabricaCostoHist for the cost history for each article.

And Comprobantes is the table of invoices (headers and details).

Normally, the ToDate is null when the cost is valid. For example:

Article FromDate      ToDate           Cost

A1       01/01/2013    31/01/2013     20

A1       01/02/2013    30/06/2013     22

A1       01/07/2013     <null>            25

Means that 25 is the valid cost for article A1 today.

But in the script I wrote the following to avoid this case:

(IsNull(ToDate), MakeDate(Year(Today()), 12, 31), ToDate)

Replacing the NULL by the last day of the current year.

Ops, can't find a way to add an attachment...

Thanks a lot.

Regards

Damian.

[stabben23]

Hi,

The first thing, will this (IsNull(ToDate), MakeDate(Year(Today()), 12, 31), ToDate)  gives you 31/12/2013? Otherwise you will have problem to catch ToDate on that row. Make sure that you have exact the same format in your date fields.

//Staffan

[#9]

You right. I left the LOAD as before, without the IsNull. It's fine now.

But, I need something like that, because the MinMaxDate table doesn't all the days, it's stop when a NULL date is found. For the previous example:

Article FromDate      ToDate           Cost

A1       01/01/2013    31/01/2013     20

A1       01/02/2013    30/06/2013     22

A1       01/07/2013     <null>            25

I get the following MinMaxDate table:

Article     Date               MinDate     MaxDate          Cost

A1          01/01/2013     01/01/2013     31/01/2013     20

A1

02/01/2013

01/01/2013

31/01/2013

20

and so on until 31/01/2013

A1          01/02/2013     01/02/2013     30/06/2013     22

A1

02/02/2013

01/02/2013

30/06/2013

22

and so on until 30/06/2013

A1     <null>                01/07/2013       <null>         25

but nothing else, no more days

Regards.

Damian.

[stabben23]

Hi, you can use this to get a ToDate if there is a null value. today +1 will give you 13/12/2013

if(isnull(ToDate),today()+1,ToDate) as ToDate

Otherwise it looks ok., keep up the good work.

//Staffan

[#11]

Thanks Staffan.

This way also gives me the error

OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocating 2 MB

I also try this way

if(isnull(ToDate),Date(today()+1),ToDate) as ToDate

but I get the error also...

I get the date from an ODBC connection. Can you recomend something to take in mind refering to date format?

Regards

Damian.