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

       

      Facts:

      LOAD  

           resource_id as Medium_ID,  

           profile_id as %profile_id,

             '1' as TrackingCount

       

      FROM

      [..\0000_Ressources\QVD\Trackinghistory\*.qvd]

      (qvd)

      ;

           //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)

      Load

      %profile_id,

          %pool_id 

      FROM

      QVD\Profiles_Evalanche.qvd

      (qvd)

      ;

          //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)

      Load

      Medium_ID,

      pool_id as %pool_id

      resident

      Resources

      ;

       

      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:

           

          facts:

          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':

           

          facts_2:

          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