topic Re: Join two QVDs with date between two date in QlikView

Join two QVDs with date between two date

Fri, 21 Jun 2013 07:59:28 GMT

Hello,

I have two tables stored into qvd:

Article.qvd

Article	Purchase_date
A1	01/12/2012
A2	15/08/2012
A3	01/01/2013
A4	03/03/2013
A5	21/04/2013

Price.qvd

Article	Price	Start_Date	End_Date
A1	5	01/01/2012	31/12/2102
A1	7	01/01/2013	31/12/2013
A2	10	01/01/2012	30/06/2012
A2	8	01/07/2012	31/12/2103
A3	2	01/01/2013	31/12/2013
A4	12	01/01/2013	15/02/2103
A4	13	16/02/2013	28/02/2103
A4	15	01/03/2103	15/03/2013

What I want is to create a table with the Article, the Purchase Date and the Price (the price is retrieve depending of the validty period):

Article	Purchase_date	Price
A1	01/12/2012	5
A2	15/08/2012	8
A3	01/01/2013	2
A4	03/03/2013	15
A5	21/04/2013	-

So I need to do a join between article.qvd and price.qvd

on Article.Article = Price.Article

and Article.Purchase_Date between Price.Start_Date and Price.End_Date.

I know how to do this in SQL but not with QVD file.

Can someone help me?

Thanks

Re: Join two QVDs with date between two date

Fri, 21 Jun 2013 08:16:52 GMT

You can use interval match..

Go through this link..

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Re: Join two QVDs with date between two date

Fri, 21 Jun 2013 08:23:08 GMT

try using the intervamatch on Purchase_date like this:

Article:

LOAD * INLINE [

Article, Purchase_date

A1, 01/12/2012

A2, 15/08/2012

A3, 01/01/2013

A4, 03/03/2013

A5, 21/04/2013

];

PriceList:

LOAD * INLINE [

Article, Price, Start_Date, End_Date

A1, 5, 01/01/2012, 31/12/2102

A1, 7, 01/01/2013, 31/12/2013

A2, 10, 01/01/2012, 30/06/2012

A2, 8, 01/07/2012, 31/12/2103

A3, 2, 01/01/2013, 31/12/2013

A4, 12, 01/01/2013, 15/02/2103

A4, 13, 16/02/2013, 28/02/2103

A4, 15, 01/03/2103, 15/03/2013

];

Price:

Left Join (Article)

IntervalMatch(Purchase_date)

LOAD Start_Date,

End_Date

Resident PriceList;

Re: Join two QVDs with date between two date

Fri, 21 Jun 2013 09:15:16 GMT

Thank you for your quick answer.

But the result of the intervalMatch is not what I expect.

How can I suppress unnecessary rows?

Re: Join two QVDs with date between two date

Fri, 21 Jun 2013 09:24:32 GMT

in the Table Box you can supress the NULL values in the presentation tab.

Re: Join two QVDs with date between two date

Fri, 21 Jun 2013 09:34:31 GMT

I know but in fact I don't want to use a table. I need to stored into a table only the row with the good price for the purchase date.

With the intervalmatch, it seems that we have a Cartesian Product.

And for Example, for the Article A4, I don't have the good result, even if I suppress the Row if the field is Null.

I think that my problem is due to the join on the key Article but i don't find a solution.

Do you know how to solve this problem?

Re: Join two QVDs with date between two date

Fri, 21 Jun 2013 11:57:24 GMT

Finally,

I find this solution:

Article:

LOAD * INLINE [

Article, PD

A1, 01/12/2012

A2, 15/08/2012

A3, 01/01/2013

A4, 03/03/2013

A5, 21/04/2013

];

left join

LOAD * INLINE [

Article, Price, SD, ED

A1, 5, 01/01/2012, 31/12/2102

A1, 7, 01/01/2013, 31/12/2013

A2, 10, 01/01/2012, 30/06/2012

A2, 8, 01/07/2012, 31/12/2103

A3, 2, 01/01/2013, 31/12/2013

A4, 12, 01/01/2013, 15/02/2013

A4, 13, 16/02/2013, 28/02/2013

A4, 15, 01/03/2013, 15/03/2013

];

Price:

Load Article, Price, PD

Resident Article

where PD >= SD

and PD <= ED;

drop table Article;

Thanks everybody for the help!