Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
];