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.

[Anonymous]

Damian

Looks like you are need of the IntervalMatch() function.

This Blog Post IntervalMatch by hicexplains it well.

Best Regards,     Bill

[#3]

Hi Bill.

Mmmhhhh... not sure. I tried it, but it just left me a table with dates, and I need more than that, because of the article.

I tried this way:

IntervalMatchCostArticlesHist:

    IntervalMatch([CreationDate]) Load distinct [FromDate], [ToDate] Resident CostHistory;

What you think?

Regards. Damian.

[stabben23]

Hi, try this, first min/max on all Items, then Intervallmatch.

MinMaxDate:

Load distinct
Item,

min(Date1) as MinDate,
max(Date2) as MaxDate
resident Your main table

group by Item

order by Item asc

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

Calendar:
Load recno()+$(vMinDate)-1 as Date
Autogenerate vMaxDate - vMinDate;

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

Drop Table Test;
Drop Table Calendar;

[#5]

Sounds good... I'll try it and let you know...

Thanks a lot.

Damian

[#6]

Staffan... I thing I missed something ...

I have the MinMaxDate table... and now what?

Still can´t add the valid cost to my main table... what do I need to do now?

Regards.

Damian.

[stabben23]

Hi, put in the cost and and then a group by on Cost

MinMaxDate:

Load distinct
Item,

Cost

min(Date1) as MinDate,
max(Date2) as MaxDate
resident Your main table

group by Item, Cost

order by Item asc

[stabben23]

Ok, to fast answer from me, will have one more look, sorry.

[stabben23]

Hi, Put in Cost as I told you, now you have a mapping table. You can now use this table with applymap and "put" in your cost into your Invoice table. Now create a key with combination Item & Date, use this in a Mapping Load.

And then map the cost into your Invoice table with the same combination.

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

[#10]

Hi Staffan. I'm not an expert on QV, so, I'm still confused. Let's see, I need:

1) MinMaxDate table grouping by item and cost.

2) I still need the calendar table? Because the field named "date" used for the key (table temp) is on calendar table...

3) then the map table

4) then I need to add the cost to the invoice table. You accomplish this using the applymap function?

Regards.

Damian.