Link 2 tables with closest lower date

[#1]

Hi everybody,

I have 2 tables: 1 is a cost table and 1 is an order table(see below)

Cost table					Order table
Item	StdCost	Startdate			Item	OrderNum	OrderDate
shoe	2.55	20080923			shirt	10	20081215
shoe	2.57	20090101			shoe	11	20090228
shoe	2.6	20090601			shoe	12	20090306
shirt	3.33	20081201			pants	13	20090611
shirt	3.4	20090201			shirt	14	20090612
pants	4.1	20090213			shoe	15	20090701
pants	4.2	20090610			pants	16	20090701
pants	4.3	20090730			pants	17	20090815
pants	4.4	20091101			shirt	18	20091123

I need to retrieve the stdcost of an item for each orderNum.

The cost table works this way: The stdCost of a shoe is 2.55 between 20080923 and 20083112.

It is 2.6 between 20090601 and today.

The resulting table should look like below:

Resulting table
Item	OrderNum	OrderDate	StdCost
shirt	10	20081215	3.33
shoe	11	20090228	2.57
shoe	12	20090306	2.57
pants	13	20090611	4.2
shirt	14	20090612	3.4
shoe	15	20090701	2.6
pants	16	20090701	4.2
pants	17	20090815	4.3
shirt	18	20091123	3.4

I can't find any example in the forum that would show me how to do this.

Thanks in Advance.

Chris

[#2]

Where/how is you source data stored?

[#3]

Nigel,

It's on an AS400.

[#4]

OK, I have no experience with that so I'm not sure I can help.

If it was SQL then I would recommend doing the Join in the SQL Select statement and loading a single table into QVW, unless there's a good reason not to do this.

Either way, I believe you are going to need to sort the problem out at the load script side, not on the front end.

[#5]

Nigel,

I would be interested to learn how to do this with 2 excel tables too( look at a working example).

Thanks

[#6]

If your Cost table has an item number associated with the item I would link the two tables by item number. So you could link the item number from the order to the item number in your cost table.  Being your data resides on an AS/400 (as ours does). I would have the data sent from the AS/400 as a CSV text file and import them into QlikView. This is what we do. The AS/400 developer just would need to do a simlpe CL program and send the files to the IFS where QlikView can get the CSV files.

Something like this:

CPYTOIMPF FROMFILE(COSTTABLE) +

                           TOSTMF('AS400Files/COSTTABLE.TXT') +

                           MBROPT(*REPLACE) FROMCCSID(37) +

                           STMFCODPAG(*PCASCII) RCDDLM(*CRLF)

CHGAUT OBJ('/AS400Files/COSTTABLE.TXT') USER(YOURUSER) +

                       DTAAUT(*RX) OBJAUT(*ALL)

Then you can manipulate the data however you need. But like I said (having worked in a reatil envionrment before) - linking by an Item number is going to be your best bet.

[calvindk]

I would add in the enddate to the cost table and then do intervalmatch.

Best wishes