The years are different in Benchmark vs Portfolio. A match would never occur.
I changed the years in Portfolio, and created two possible script solutions in the attached document.
Solution 1 reduces the portfolio entries to include only those that appear in the Benchmark table.
Solution 2 adds Benchmark information to the Portfolio table so that you can check which Portfolio entries do have a Benchmark entry, and which do not.
Test not exists 2.qvw 151.8 K
Peter, I think you just handed me the Darwin award of the thread (I still have the reflex of writing 2014 sometimes...)
BUT, I need a result table, and your solution doesn't give that. Sorry, this is part of a more complex scheme that requires an intermediary table.
Both your answers are perfect Michael and Manish, I put up the correct marker on Manish since the script was more explicit
Thank you very much guys
Now as a follow up question, if my benchmark was not populated for 2015-02-15, but only 2015-02-16. I would need to take for granted that my portfolio is evaluated against 2015-02-14.
What solution is better, have my benchmark table filled for unpopulated dates with the previous day's values, or is there a work around in the script where I could try to match on the closest date below it's portfolio date value?
LOAD *, Date & CUSIP_BENCHMARK as KEY1 INLINE [
LOAD *, VAL_Date & CUSIP_PORTFOLIO as KEY2 INLINE [
VAL_Date as DATE_RESULT,
CUSIP_PORTFOLIO as CUSIP_RESULT
Where NOT EXISTS(KEY1, KEY2);
DROP table Benchmark ;
Drop TABLE PORTFOLIO;