join based on previously loaded table (where exists)

[#1]

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 @@

[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