Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Left join two RESIDENT tables WITHOUT further SQL calls

     I have been searching the forum for hours for a single example of what I need.  Every join statement I have come across does an "if exists" on a SQL load.  I don't want to go back to the database for data that I already have in memory.

I have two resident tables with a common field.  All values of this field appear in one table.  the other table has only *some* of the values.  I need to join them getting all distinct values of the field and a "yes" if a value appears in both and a "no" if it appears in only one.

Tables are all_members and some_members (self explanatory).  The field common to both is MemberID.

I need a load statement that creates a *third* table with two fields - MemberID and the aforementioned 'Yes' or 'No' value.

Help please!!!! 

Thank you!!

1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

Left join two RESIDENT tables WITHOUT further SQL calls

Assuming all_members and some_members are already loaded earlier in the script:

Map_SomeMembers:

MAPPING LOAD

   MemberID,

   'Yes'

RESIDENT some_members;

New_Table:

LOAD

   MemberID,

   ApplyMap('Map_SomeMembers',MemberID,'No') AS Check

RESIDENT all_members;

That should do it.

Jason

9 Replies
Not applicable

Re: Left join two RESIDENT tables WITHOUT further SQL calls

I used the lookup() function. 

SomeMembers:

LOAD MemberID,

     Name

FROM

(ooxml, embedded labels, table is SomeMembers);

AllMembers:

LOAD MemberID,

     Name,

     if(isnull(lookup('Name', 'MemberID',MemberID, 'SomeMembers')),'No','Yes') as Test

FROM

(ooxml, embedded labels, table is AllMembers);

See attacchment.  Not sure that is what you are looking for.

jason_michaelid
Honored Contributor II

Left join two RESIDENT tables WITHOUT further SQL calls

Assuming all_members and some_members are already loaded earlier in the script:

Map_SomeMembers:

MAPPING LOAD

   MemberID,

   'Yes'

RESIDENT some_members;

New_Table:

LOAD

   MemberID,

   ApplyMap('Map_SomeMembers',MemberID,'No') AS Check

RESIDENT all_members;

That should do it.

Jason

Not applicable

Left join two RESIDENT tables WITHOUT further SQL calls

Jason's approach works too. 

Not applicable

Re: Left join two RESIDENT tables WITHOUT further SQL calls

I really appreciate the fast response!  Unfortunately, I simply do not know enough about the syntax to adapt your suggestion to my needs. 

Your lookup  only produces two tables, and they load from an external data source.  I needed examples of creating a third based on the join results of two resident tables.   I am sure that's a trivial difference to you, but it is a high hurdle for new users.  There are tons of posts here about this very topic and each one has slight differences that vastly change the solution

How about another approach - here is a sample load with simple inline data and an explanation of what I need next

ALL_MEMBERS:

Load * INLINE [

    MEMBERID, RANDOM1

    A, a

    B, a

    C, a

    D, a

    E, a

    F, a

    G, a

    H, a

    I, a

    J, a

    K, a

    L, a

    M, a

    N, a

];

SOME_MEMBERS:

Load * INLINE [

    MEMBERID, RANDOM2

    A, a

    C, a

    G, a

    L, a

    M, a

];

I added the "RANDOM*" columns so QV would not simply concatenate the two tables.

I need a table with the following:

1) All MEMBERID values from ALL_MEMBERS (aliased as anything you want)

2) 'Yes' if the MEMBERID value from ALL_MEMBERS appears in SOME_MEMBERS, 'No' if it does not.

Thank you so much for your help!  I've been beating my head against my desk all day.  Qlikview never fails to make me feel like a flailing idiot.

Not applicable

Left join two RESIDENT tables WITHOUT further SQL calls

If you need to have all three tables in the final data model then use Jason's code after you have loaded both tabled.

Mapping load is a temporary table that goes away after a reload.

ALL_MEMBERS:

Load * INLINE [

    MEMBERID, RANDOM1

    A, a

    B, a

    C, a

    D, a

    E, a

    F, a

    G, a

    H, a

    I, a

    J, a

    K, a

    L, a

    M, a

    N, a

];

SOME_MEMBERS:

Load * INLINE [

    MEMBERID, RANDOM2

    A, a

    C, a

    G, a

    L, a

    M, a

];

//now use jason's approach  I did not edit the field names but know they need to match your code above.

Map_SomeMembers:

MAPPING LOAD

   MemberID,

   'Yes'

RESIDENT some_members;

//The table above goes away  after reload

//Its important that this table has a different structure than the all members table because QlikView will concatenate it to all members.

New_Table:

LOAD

   MemberID,

   ApplyMap('Map_SomeMembers',MemberID,'No') AS Check

RESIDENT all_members;

Now you should have 3 tables all linked via MemberID.

jason_michaelid
Honored Contributor II

Re: Left join two RESIDENT tables WITHOUT further SQL calls

You need to use "NoConcatenate" to prevent QlikView joining 2 identical tables.  Try this mate:

ALL_MEMBERS:

Load * INLINE [

    MEMBERID

A

B

C

D

E

F

G

H

I

J

K

L

M

];

SOME_MEMBERS:

NoConcatenate

Load * INLINE [

    MEMBERID

    A

    C

    G

    L

    M

];

Map_Members:

MAPPING LOAD

  MEMBERID,

  'Yes'

RESIDENT SOME_MEMBERS;

FINAL TABLE:

LOAD

  MEMBERID,

  ApplyMap('Map_Members',MemberID,'No')  AS  Check

RESIDENT ALL_MEMBERS;

Stick at it - QlikView is a completely brilliant product with so much awesome functionality!!

Jason

Not applicable

Left join two RESIDENT tables WITHOUT further SQL calls

Yes, I should have mentioned that having all three tables survive was a key element of my goal.  I had a field in the Some_members table with the value "yes" for all fields so that I could drill into only those members by selecting the only available value for that field in a listbox - but there was no corresponding "no" to link to the other records that appeared in All_members but not Some_Members.  Make sense?  I needed to create this data point to have the binary yes/no to drill into one or both.

Thank you so much for the responses.  The activity in this community never ceases to amaze me.  QV obviously has a die hard following. 

Thanks again!  Cheers!

Not applicable

Left join two RESIDENT tables WITHOUT further SQL calls

I did not know the NoConcatenate trick!  Luckily I have not run into loading two tables with the same structure that I need to keep separate - the example below was just for argument's sake.  My actual data does not share structures between the two tables.  But thanks for the tip!

Not applicable

Left join two RESIDENT tables WITHOUT further SQL calls

The mapping load approached in a sample document with inline tables.  The full document is loading now.  Fingers crossed!

Community Browser