topic Joining tables in QlikView

Joining tables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Here is a simplified example of what I am doing. Suppose you have these tables below. My goal is to join the two tables so that Table 1 now has sales rep info. My issue is that table 2 (the ones with the sales rep) has some unique IDs appearing multiple times. However, I am not interested in precision, and I want the join to just bring in one match, and not all. For example. In table 1 there is a unique ID A, and if I simply join the two tables, I will get two matches for this ID but I just want to pick the first one, John smith (or any of them really). What is the best way to do this? Don't want to use ApplyMap because I have a ton fields I want to bring in to table 1. Any suggestions? Thank you

Table 1
Unique ID	Dollar Amount
A	98
B	6
C	24
D	12
E	73
F	85
G	57
H	67
I	65

Table 2
Unique ID	Sales rep
A	John Smith
A	Bob
B	Jill
C	Sam
D	Doug
E	Becca
F	Jim
G	James
H	Igor
I	Kate

Re: Joining tables

olivierrobin — Fri, 20 Jul 2018 15:44:06 GMT

hello

if you don't care of which sales rep you take

you may use a join like

left join(table1)

load Unique ID,maxstring(Sales Rep) as A_Sales_rep

from table2

group by Unique ID

Re: Joining tables

vishsaggi — Fri, 20 Jul 2018 17:49:08 GMT

Just load your both tables as is and use below in your Table2 load script like:

Table2:

LOAD *

WHERE Flag = 1;

LOAD *, IF(UniqueID = Previous(UniqueID), 0, 1) AS Flag INLINE [

UniqueID, Salesrep

A, John Smith

A, Bob

B, Jill

C, Sam

D, Doug

E, Becca

F, Jim

G, James

H, Igor

I, Kate

];