topic Left join two RESIDENT tables WITHOUT further SQL calls in QlikView

Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 18:22:50 GMT

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

Re: Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 19:04:03 GMT

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.

Left join two RESIDENT tables WITHOUT further SQL calls

Jason_Michaelides — Fri, 16 Sep 2011 19:27:46 GMT

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

Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 19:29:29 GMT

Jason's approach works too.

Re: Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 19:55:12 GMT

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.

Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 20:18:32 GMT

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.

Re: Left join two RESIDENT tables WITHOUT further SQL calls

Jason_Michaelides — Fri, 16 Sep 2011 20:20:52 GMT

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

ALL_MEMBERS:

Load * INLINE [

MEMBERID

];

SOME_MEMBERS:

NoConcatenate

Load * INLINE [

MEMBERID

];

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

Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 20:23:57 GMT

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!

Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 20:26:01 GMT

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!

Left join two RESIDENT tables WITHOUT further SQL calls

Fri, 16 Sep 2011 20:27:05 GMT

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