Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
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.
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
Jason's approach works too.
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.
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.
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
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!
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!
The mapping load approached in a sample document with inline tables. The full document is loading now. Fingers crossed!