Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join two QVDs with date between two date

Hello,

I have two tables stored into qvd:

Article.qvd

Article
Purchase_date
A101/12/2012
A215/08/2012
A301/01/2013
A403/03/2013
A521/04/2013

Price.qvd

ArticlePriceStart_DateEnd_Date
A1501/01/201231/12/2102
A1701/01/201331/12/2013
A21001/01/201230/06/2012
A2801/07/201231/12/2103
A3201/01/201331/12/2013
A41201/01/201315/02/2103
A41316/02/201328/02/2103
A41501/03/210315/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):

ArticlePurchase_datePrice
A101/12/20125
A215/08/20128
A301/01/20132
A403/03/201315
A521/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

6 Replies
Not applicable
Author

You can use interval match..

Go through this link..

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

Not applicable
Author

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;

Not applicable
Author

Thank you for your quick answer.

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

price.JPGprice_expected.JPG

How can I suppress unnecessary rows?

Not applicable
Author

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

21-06-2013 11-22-44.png

Not applicable
Author

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?

Not applicable
Author

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!