Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Reply
MVP & Luminary
MVP & Luminary

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

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