1 Reply Latest reply: Dec 18, 2015 7:11 PM by Marcus Sommer RSS

    join based on previously loaded table (where exists)

      Hello all,


      I would like to join a %pool_id to a table based on two different conditions:


      Step 0. Load Facts




           resource_id as Medium_ID,  

           profile_id as %profile_id,

             '1' as TrackingCount






           //Step 1. check whether in the loaded facts table a %profile_id exists and match this to the profiles table where a link between %profile_id           //and %pool_id exists


      join (Facts)








          //Step 2. Since not all facts have a %profile_id and, additionally, some %profile_id cannot be mapped to a %pool_id, I would like to use the      //%pool_id that are mentioned in a Resources table (where the different mediums are described) as an additional way to connect the      //tracking entry via its Medium_id to the %pool_id


      join (Facts)



      pool_id as %pool_id





      Intuitively, I would do step 1 and afterwords join step 2 on the condition that no %pool_id was joined during step 1. However, I do not know how I can reference back to a previously loaded table (i.e. the Facts table).


      Can anyone please help? I would very much appreciate a fast anwer as this topic has to be handed in on Monday @@

        • Re: join based on previously loaded table (where exists)
          Marcus Sommer

          I believe you need a left join between Trackinghistory and Profiles_Evalanche and technically you could do a join to a source-table like:



          Load * From x;

               (left/right/inner) join

          Load Fields ... Resident facts;


          but in your case it seems to make no sense or at least I didn't understand it. Maybe you need now more a second load from facts to fill the NULL from the missing %pool_Id with the Medium_ID or a string like 'NULL':



          noconcatenate load

               Medium_ID, %profile_id, TrackingCount,

               if(len(trim(%pool_id))=0, 'NULL', %pool_id) as %pool_id

          Resident facts;

          drop table facts;


          - Marcus