NOT EXISTS grouped by date

[sibrulotte]

Hi,

here is a simple inline load you can try to help me out:

Benchmark:

LOAD * INLINE [

    Date, CUSIP_BENCHMARK

    2015-02-14, A

    2015-02-14, B

    2015-02-14, C

    2015-02-14, D

    2015-02-14, E

    2015-02-14, F

    2015-02-15, A

    2015-02-15, C

    2015-02-15, D

    2015-02-15, E

    2015-02-15, F

    2015-02-15, G

];

PORTFOLIO:

LOAD * INLINE [

    VAL_Date, CUSIP_PORTFOLIO

    2014-02-14, A

    2014-02-14, B

    2014-02-14, C

    2014-02-14, D

    2014-02-14, E

    2014-02-14, G

    2014-02-15, A

    2014-02-15, B

    2014-02-15, C

    2014-02-15, D

    2014-02-15, E

    2014-02-15, G

];

As you can see, I have portfolio values, for two dates, 14th and 15th.

I want to see if CUSIP values in my portfolio are in the benchmark, for a specific date.

But my small little knowledge of Qlikview only knows the NOT EXISTS function and it scans the whole table, it doesn't group by date for example.

Result:

load

VAL_Date as DATE_RESULT,

CUSIP_PORTFOLIO as CUSIP_RESULT

resident PORTFOLIO

where NOT EXISTS(CUSIP_BENCHMARK,CUSIP_PORTFOLIO)

;

DROP table Benchmark ;

How do I get my result table to show CUSIP --> G for 2014-02-14. (CUSIP in portfolio, but not existing for 2014-02-14 in the benchmark).

[Peter_Cammaert]

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.

Best,

Peter

[Anonymous]

You can create a  new field in the Benchmark table:

Benchmark:

LOAD *, Date & CUSIP_BENCHMARK as ExistInBenchmark  INLINE [

    Date, CUSIP_BENCHMARK

    2015-02-14, A

    ....

So, in the condition you can use it:

where NOT EXISTS(ExistInBenchmark, ValDate & CUSIP_PORTFOLIO)

[MK_QSL · Accepted solution]

Benchmark:

LOAD *, Date  & CUSIP_BENCHMARK as KEY1  INLINE [

    Date, CUSIP_BENCHMARK

    2014-02-14, A

    2014-02-14, B

    2014-02-14, C

    2014-02-14, D

    2014-02-14, E

    2014-02-14, F

    2015-02-15, A

    2015-02-15, C

    2015-02-15, D

    2015-02-15, E

    2015-02-15, F

    2015-02-15, G

];

PORTFOLIO:

LOAD *, VAL_Date  & CUSIP_PORTFOLIO as KEY2 INLINE [

    VAL_Date, CUSIP_PORTFOLIO

    2014-02-14, A

    2014-02-14, B

    2014-02-14, C

    2014-02-14, D

    2014-02-14, E

    2014-02-14, G

    2015-02-15, A

    2015-02-15, B

    2015-02-15, C

    2015-02-15, D

    2015-02-15, E

    2015-02-15, G

];

NoConcatenate

Result:

Load

  KEY2,

  VAL_Date as DATE_RESULT,

  CUSIP_PORTFOLIO as CUSIP_RESULT

Resident PORTFOLIO

Where NOT EXISTS(KEY1, KEY2);

DROP table Benchmark ;

Drop TABLE PORTFOLIO;

[sibrulotte]

Thanks:

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?