Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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

Re: Join two QVDs with date between two date

You can use interval match..

Go through this link..

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

Not applicable

Re: Join two QVDs with date between two date

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

Re: Join two QVDs with date between two date

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

Re: Join two QVDs with date between two date

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

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

Not applicable

Re: Join two QVDs with date between two date

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

Re: Join two QVDs with date between two date

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!

Community Browser