Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 @@
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