Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can use interval match..
Go through this link..
http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch
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;
Thank you for your quick answer.
But the result of the intervalMatch is not what I expect.
How can I suppress unnecessary rows?
in the Table Box you can supress the NULL values in the presentation tab.
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?
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!