join based on previously loaded table (where exists)

Fri, 18 Dec 2015 22:27:26 GMT

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 — Sat, 19 Dec 2015 00:11:27 GMT

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

topic Re: join based on previously loaded table (where exists) in QlikView

join based on previously loaded table (where exists)

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