4 Replies Latest reply: Feb 16, 2015 1:35 PM by Simon Brulotte RSS

    NOT EXISTS grouped by date

    Simon Brulotte

      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

          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
              Simon Brulotte

              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?

            • Re: NOT EXISTS grouped by date
              Michael Solomovich

              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
                Manish Kachhia

                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;