topic Re: How to combine table batch master and table rate master based on wef date and weto date in QlikView

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

vikasmahajan — Wed, 28 Aug 2013 06:55:30 GMT

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

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

Wed, 28 Aug 2013 07:50:38 GMT

Hi Vikas,

I've attached a sample document to show you how interval match works with your data. However, I have had to change it slightly. Your key field [PROD_BATCH] needed common values in the two tables. In the spreadsheet you sent, there were no matches.

In the attached, I have just used Join which defaults to outer join but you could use other joins depending on what you want to do.

Andy

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

vikasmahajan — Wed, 28 Aug 2013 07:56:22 GMT

Thanks for Giving such NICE Explanations.!!!!!!!!!

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

vikasmahajan — Wed, 28 Aug 2013 12:05:09 GMT

Hii

Attaching my qvw for the same logic is not working could you check please where I am Going wrong.

Thanks

vikas

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

Wed, 28 Aug 2013 12:10:42 GMT

Hi Vikas,

For some reason I cannot open the .qvw after downloading? Could you post your script on here and I can have a look.

Andy

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

vikasmahajan — Wed, 28 Aug 2013 12:18:19 GMT

i ATTACHED THE SAME PLEASE REPLY ME.

VIKAS

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

abeyphilip — Wed, 28 Aug 2013 12:28:37 GMT

Hi Vikas,

Created this when you first posted the issue. Then saw you got the soln. See if it helps.

I am am not able to modify your QVW and reload because of the QVDs in the script.

Rgds,

Abey

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

vikasmahajan — Wed, 28 Aug 2013 12:31:03 GMT

Thanks , I will try and get back to you .

vikas

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

Wed, 28 Aug 2013 12:35:00 GMT

I think you want something like this. Please find attached. You had renamed your key fields so they won't match.

Andy

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

vikasmahajan — Wed, 28 Aug 2013 13:06:34 GMT

I have try this also not works for me , Actually prod code is common field sorry I forgot to put in Rate MAster file

so how to implement the same with product code.

Thanks

Vikas

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

abeyphilip — Wed, 28 Aug 2013 13:42:17 GMT

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

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

vikasmahajan — Thu, 29 Aug 2013 06:07:39 GMT

Hii

Please find my qvw attached where I am Going wrong.

Vikas

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

Thu, 29 Aug 2013 07:21:39 GMT

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

Andy

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

vikasmahajan — Thu, 29 Aug 2013 07:40:24 GMT

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

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

Thu, 29 Aug 2013 08:07:57 GMT

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/>

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

vikasmahajan — Thu, 29 Aug 2013 09:47:43 GMT

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/