Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sibrulotte
Not applicable

NOT EXISTS grouped by date

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

1 Solution

Accepted Solutions
MK_QSL
Not applicable

Re: NOT EXISTS grouped by date

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;

4 Replies
Peter_Cammaert
Not applicable

Re: NOT EXISTS grouped by date

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

mov
Not applicable

Re: NOT EXISTS grouped by date

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
Not applicable

Re: NOT EXISTS grouped by date

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
Not applicable

Re: NOT EXISTS grouped by date

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?