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,

Henric Cronström have mada a good one: QlikView Date fields

[#3]

No way. I tried several functions, nesting Date and Date#, but still getting the error.

I also UPDATEd all dates in the DB to be a valid date, no NULL value left. Then I eliminated all functions in the QV script. This way, the MinMaxDate table loaded much more records (30 millions), but still get the error.

What else can I do?

Regards.

Damian.

[stabben23]

Hi Damian,

its hard to say, try to take it one step at the time, you need to get correct format on your date and then in the peek use the num function.

If you can attach the file it would be easier.

//Staffan

[#5]

Thanks, I'm trying to build a smaller qvw, to upload it here.

I read the DATE fields from a DB... so, as far as I know, there must not be any problem... QV must interpret them as date...

Staffan, see the table ArticulosFabricaCostoHist and your suggestion in the tab named "Costo Histórico", sub AgregarCostoComprobantesCliente.

Thanks again.

Regards.

Damian.

[#6]

Finally resolved using method 2 on page 5 of the excellent document "IntervalMatch and Slowly Changing Dimensions.pdf"

Thanks a lot Staffan and Bill.

Best regards.

Damian