How to combine table batch master and table rate master based on wef date and weto date

[vikasmahajan]

Dear all

Please refer attached excel sheet My requirement is   to combine table batch Master  with rate master

based on effective dates from and effective dates , How I can do the same in sql or qlikview.

Thanks

Vikas

[abeyphilip · Accepted solution]

Vikas,

With product code as third common field? I have created the same assuming this.

How it works is:

you do interval match using the these fields:

Date field that you do interval match on.

Then the common fileds

eg: - INTERVALMATCH (EFFDATE,PROD_BATCH,PROD_CODE)

Then include all those common fields and date range from the date range table.

left join (Batch_Master)

INTERVALMATCH (EFFDATE,PROD_BATCH,PROD_CODE)

LOAD

distinct PB_EFF_FROM,

PB_EFF_TO

,PROD_BATCH,

PROD_CODE

RESIDENT Rate_Master;

Then you can left join the rest of the fields separately.

If the issue is still there then please post the QVW that your are trying with inline data (no QVDs) and I will try to fix on that.

Rgds,

Abey

[vikasmahajan]

Hii

Please find my qvw attached where I am Going wrong.

Vikas

[#4]

Try this. I've put notes in the script.

Andy

[vikasmahajan]

Hello

please note my requirement is to  combine both tables based on  wef from & Wef to dates along with batch nos

base on common key product code. 01/11/2007 and product 1ytd have batch  (YD 2K7B)  should display PB_NBR (45.14)

this should merged.

PROD_CODE	PROD_BATCH	PD	SD	ED	PB_NBR
1YD1	NULL		01/11/2007	30/09/2008	45.14
1YD1	NULL		01/10/2009	01/03/2010	46.3
1YD1	NULL		02/03/2010	01/07/2010	46.77
1YD1	YD 2K7B	01/11/2007	01/11/2007	30/09/2008
1YD1	YD 2L7B	01/11/2007	01/11/2007	30/09/2008
1YD1	YD 3F7B	01/11/2007	01/11/2007	30/09/2008
1YD1	YD 3F8B	01/10/2009	01/10/2009	01/03/2010
1YD1	YD 3G8B	02/03/2010	02/03/2010	01/07/2010
1YD1	YD 3H7B	01/10/2009	01/10/2009	01/03/2010
1YD1	YD 4E6V	02/03/2010	02/03/2010	01/07/2010

[#6]

The problem may be in your base data. It is hard to get a complete solution without looking at that. Can you attach it and send it across so I can see what data you’re trying to join?

Andy Myers

Business Systems Analyst

APPH

8 Pembroke Court, Manor Park, Runcorn, Cheshire, WA7 1TG, England

Tel. +44 (0) 1928 530 573 | IP Phone. 30573

andrew.myers@apph.com | http://www.apph.com<http://www.apph.com/>

[vikasmahajan]

Thanks Andy You are Right There is some problem in excel , Solution given by

Abey Chandy  works for me thanks to all of you.

Vikas

Following Link Explain Details for Interval Match

http://www.citagus.com/citagus/blog/interval-match-feature-in-qlikview/