6 Replies Latest reply: Jul 10, 2014 3:08 PM by Massimo Grossi RSS

    Flags count

    Koushik Vutha

      Hi

       

      I have two tables 'Table1' with 20 records and 'Table2' with 8 records.

      When I left join Table2 to Table1 how do I flag those records in Table1 that DOESNOT match. From the example above I need to flag 12 records. From the below script I can count the flag to get 8 records.

       

      Please see the script I have used below.

       

       

      Table1:

      Load

      unique id,

      Name

      from...

       

      Left join (Table1)

      Load

      unique id,

      type

      1 as flag
      from...

        • Re: Flags count
          Alessandro Saccone

          You cannot because left join from Tab2 on Tab1 "Exclude" records not existing in Tab2 (Tab2 is the starting set of data)

           

          To achive the result you need (the easiest way but there are several ways)

           

          1) join the tables so you have all the combinations among records

          2) load again the resulting table in order to flag what you need (something like: if(isnull(fieldfromTab1),'1','0') as flag)

           

          Let me know

          • Re: Flags count
            Srikanth P

            In this scenario, Left join is not give what do you need.

             

            First do the Join and create the Flag on resident load like below:

             

            Temp:

            LOAD

                 UniqueKey,

                 UniqueKey AS Flag,

                 Name

            From Table1;

            Join (Temp)

            LOAD

                 UniqueKey,

                 Type

            From Table2;

             

            Final_Table:

            Noconcatenate

            LOAD

                 UniqueKey,

                 IF(isNull(Flag) ,1 ,0) AS Flag,

                 Name.

                 Type

            Resident Temp;

             

            DROP Table Temp;

              • Re: Flags count
                Koushik Vutha

                True. But the problem is I have more thn 3 tables and to take the resident after the joins is painful as the tables have millions of records and I get an error saying 'Exceeding allocated memory 2MB' etc...

                 

                Table1:

                Load

                Unique id

                field1.1

                field1.2

                 

                Left join (Table1)

                Unique id

                field2.1

                field2.2

                 

                concatenate Table1

                Load

                Unique id2

                field3.1

                field3.2

                 

                Left join (Table1)

                Unique id2

                field4.1

                field4.2

                  • Re: Flags count
                    Srikanth P

                    It doesn't matter how many tables you have. As per your script don't do concatenate (concatenate Table1) at first place. Create the temp tables first and finally concatenate the tables.

                     

                    Please post full table details so community will help more.

                • Re: Flags count
                  Jane Jackman

                  Try

                  mapT2a:

                  Mapping load

                  unique id,

                  type

                  from ....

                   

                   

                  Table1:

                  Load

                  unique id,

                  Name,

                  if (tmpType<>0,tmpType) as Type,

                  if (tmpType=0,0,1) as flag

                  ;

                  Load

                  unique id,

                  Name,

                  applymap('mapT2a',unique id,0) as tmptype

                  from...

                  ;

                  • Re: Flags count
                    Massimo Grossi

                    t1: load rowno() as unique_id, 't1 name ' & rowno() as name AutoGenerate 20;

                    t2: load rowno() as unique_id, 't2 type ' & rowno() as type AutoGenerate 8;

                     

                    Map:

                    Mapping load unique_id, 0 Resident t2;

                     

                    Right join (t2)

                    load

                      *,

                      applymap('Map', unique_id, 1) as flagnotmatch

                    Resident t1;

                     

                    DROP Table t1;

                     

                    1.png