1 Reply Latest reply: Apr 21, 2017 3:49 AM by Hakan Ronningberg RSS

    Subselect with Join for SAP KONV Table

    Matthew Werner

      I am working on building a load script for KONV that loads records based on changes occurring at the PO line item level (EKPO).  Below is the subselect query that I am trying to use:

       

       

      SQL SUBSELECT KSCHL KOPOS ... 
      FROM KONV 
      WHERE KNUMV IN (
      SELECT h~KNUMV AS KNUMV
      FROM EKKO AS h
      INNER JOIN EKPO AS l ON h~EBELN = l~EBELN
      WHERE l~AEDAT BETWEEN '20080601' AND '20160630' AND l~LOEKZ <> 'L'
      );

       

       

       

      However, it appears that I can't perform joins within a subselect.  I also tried performing the join first and loading it into a table in memory and then using that table within the subselect as follows:

       

      KNUMV_TEMP:

       

      SQL SELECT h~KNUMV AS KNUMV

      FROM EKKO AS h

      INNER JOIN EKPO AS l ON h~EBELN = l~EBELN

      WHERE l~AEDAT BETWEEN '20080601' AND '20160630' AND l~LOEKZ <> 'L';

       

       

      SQL SUBSELECT KSCHL KOPOS ... 
      FROM KONV 
      WHERE KNUMV IN (

      SELECT KNUMV

      RESIDENT KNUMV_TEMP

      );

       

      This also doesn't seem to work.  Is something like this even possible in QlikView? I appreciate any feedback/thoughts/help that anybody out there might have.

       

       

       

        • Re: Subselect with Join for SAP KONV Table
          Hakan Ronningberg

          Hi Matthew,

           

          Unfortunately the Subselect method does not support joins. Hope you can solve it some other way!

           

          Also note that the usage of 'AS' in select statements is not supported by the SQL connector.

           

          You can write:

           

          SQL SELECT EKKO~KNUMV

          FROM EKKO

          INNER JOIN EKPO ON EKKO~EBELN = EKPO~EBELN

          WHERE EKPO~AEDAT BETWEEN '20080601'

          AND '20160630' AND EKPO~LOEKZ <> 'L';

           

          Regards,

          Hakan