Solved: Re: Re: How to combine table batch master and tabl... - Qlik Community

vikasmahajan · ‎2013-08-28

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

abeyphilip · ‎2013-08-28

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

View solution in original post

Report Inappropriate Content · ‎2013-08-28

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

vikasmahajan · ‎2013-08-28

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

vikasmahajan · ‎2013-08-28

Hii

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

Thanks

vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

Report Inappropriate Content · ‎2013-08-28

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

vikasmahajan · ‎2013-08-28

i ATTACHED THE SAME PLEASE REPLY ME.

VIKAS

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

abeyphilip · ‎2013-08-28

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

vikasmahajan · ‎2013-08-28

Thanks , I will try and get back to you .

vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

Report Inappropriate Content · ‎2013-08-28

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

Andy

vikasmahajan · ‎2013-08-28

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

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