2 Replies Latest reply: Feb 24, 2014 4:42 AM by Peter Cammaert RSS

    lookup within tables and result sets

    Marko Rakar

      I have following situation:

       

      "Master table":

      LOAD * INLINE [

          ID, Name

          3021, B

          3031, C

          3051, D

          3061, G

      ];

       

      "Table A":

      LOAD * INLINE [

          ID, Property_1

          3021, B

          3031, C

          3051, D

          3061, G

      ];

       

      "Table B":

      LOAD * INLINE [

          ID, Property_2

          3021, A

          3031, F

          3051, M

          3061, X

      ];

       

      "Table for lookup":

      LOAD * INLINE [

          ID, Lookup_Property

          3021, B

          3031, F

          3051, L

          3061, T

      ];

       

      My idea is following;

       

      I want to find all instances of ID where value set in field Lookup_Property does not show in fields Property_1 or Property_2.

       

      So, in this particular case, my result set should include IDs 3051 and 3061 (in the case of ID 3021 "B" shows up in Table A and in the case of ID 3031 it shows up in Table B)

        • Re: lookup within tables and result sets
          Sunil Chauhan

          "Table for lookup":

          Load * from table name;

           

          "Table A":

           

          load *

           

          from table name where not Exists(Name,Property_1);

          Concatenate

          "Table B":
          Load * from tablename where not Exists(Name,Property_2);

           

           

          drop table "Table for look up";

          apply this in ur code

          hope this helps

          • Re: lookup within tables and result sets
            Peter Cammaert

            Concatenate Table A and Table B and turn it into a mapping table like:

             

            MergedTable:

            LOAD ID & '-' & Property_1 AS Index, 1 AS Flag

            RESIDENT [Table A];

             

            CONCATENATE (MergedTable)

            LOAD ID & '-' & Property_2 AS Index, 1 AS Flag

            RESIDENT [Table B];

             

            AvoidThese:

            MAPPING LOAD * RESIDENT MergedTable;

             

            DROP Table MergedTable;

             

            Now when transferring data from one place to another (e.g. from [Table for lookup] to somewhere else) use applymap() to check whether we should really keep the current record.

             

            FilteredTable:

            NOCONCATENATE LOAD *

            RESIDENT [Table for lookup]

            WHERE IsNull(applymap('AvoidThese', ID & '-' & Lookup_Property, NULL());

             

            There ar eother solutions as well.

             

            Peter