Matching dates between different tables

Wed, 11 Nov 2015 17:25:11 GMT

Hi everybody,

I am trying to match dates between two different tables.

I have one table with the following fields:

Inv_dates:

Part_Part,

Inv_date

and second table with :

Cost_cal_date:

Part ,

Cost_cal_date

The data in these tables is , as an example :

23	1/1/2013
78	1/2/2013
78	25/02/2013
88	3/3/2013
88	5/3/2013

23	31/12/2012
23	31/01/2013
23	28/02/2013
23	31/03/2013
23	30/04/2013
23	31/05/2013
78	31/12/2012
78	31/01/2013
78	28/02/2013
78	31/03/2013
78	30/04/2013
78	31/05/2013
88	31/12/2012
88	31/01/2013
88	28/02/2013
88	31/03/2013
88	30/04/2013
88	31/05/2013

What I am trying to do is to on the first table, for each line, the most close COST_CAL_DATE from the second table but which is earlier than the date on the line. for example

23	1/1/2013	31/12/2012
78	1/2/2013	31/01/2013
78	25/02/2013	31/01/2013
88	3/3/2013	28/02/2013
88	5/3/2013	28/02/2013

I have tried to first join both tables, with left join and than to read from the same table for a new one with

'where max(COST_CAL_DATE) <= INV_DATE' but it didn't work.

I also tried to work with the INTERVALMATCH function but it didn't work.

I will appreciate any help.

Thank You,

Osher Sassoni.

Re: Matching dates between different tables

settu_periasamy — Thu, 12 Nov 2015 03:36:06 GMT

Hi,

Try the below script..

Inv:

LOAD * INLINE [

Part, Inv_dates

23, 1/1/2013

78, 2/1/2013

78, 2/25/2013

88, 3/3/2013

88, 3/5/2013

];

Left Join(Inv)

Cost:

LOAD * INLINE [

Part, Cost_cal_date

23, 12/31/2012

23, 1/31/2013

23, 2/28/2013

23, 3/31/2013

23, 4/30/2013

23, 5/31/2013

78, 12/31/2012

78, 1/31/2013

78, 2/28/2013

78, 3/31/2013

78, 4/30/2013

78, 5/31/2013

88, 12/31/2012

88, 1/31/2013

88, 2/28/2013

88, 3/31/2013

88, 4/30/2013

88, 5/31/2013

];

NoConcatenate

New:

LOAD Part,

Inv_dates,

Max(Cost_cal_date) as Cost_cal_date

Resident Inv Where Cost_cal_date<=Inv_dates Group by Part,Inv_dates;

DROP Table Inv;

topic Re: Matching dates between different tables in QlikView

Matching dates between different tables

Re: Matching dates between different tables