topic Re: NOT EXISTS grouped by date in QlikView

NOT EXISTS grouped by date

sibrulotte — Mon, 16 Feb 2015 16:53:30 GMT

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).

Re: NOT EXISTS grouped by date

Peter_Cammaert — Mon, 16 Feb 2015 17:31:44 GMT

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

Re: NOT EXISTS grouped by date

Anonymous — Mon, 16 Feb 2015 17:34:09 GMT

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)

Re: NOT EXISTS grouped by date

MK_QSL — Mon, 16 Feb 2015 17:38:13 GMT

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;

Re: NOT EXISTS grouped by date

sibrulotte — Mon, 16 Feb 2015 18:35:02 GMT

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?