A problem with exists()...

[#1]

Take this example:

Table1:
LOAD * INLINE [
 Letter, Amount
 A, 50
 B, 75
 C, 100
];

Table2:
LOAD *, if(exists(Letter),1,0) as 'Flag' INLINE [
 Letter, Invoice Number
 A, 1
 A,2
 D,16
 D, 14
];

That will how the first D in table 2 as a flag of 0, and the second D will have a flag of 1 because it exists in that table. However, what I really want to say is:

"if Letter exists in Table1, 1, else 0"

Is there any way to do that? I don't see anything in the reference manual about specifying a table name in the exists function which shocks me... surely there is a way to do this?

Thanks.

[#2]

Seconds after posting this I realized I should probably just use MAPPING LOAD for the first table and applymap( instead of exists... but is that the best way?

[johnw · Accepted solution]

I can't tell you if it's the best way as I have no idea what you're trying to accomplish. However, a standard approach is to add a copy of the key value to the first table, do an exists on that copy, then drop the copy.

Table1:
LOAD *,Letter as T1Letter INLINE [
Letter, Amount
A, 50
B, 75
C, 100
];
Table2:
LOAD *, if(exists(T1Letter,Letter),1,0) as Flag INLINE [
Letter, Invoice Number
A, 1
A,2
D,16
D, 14
];
DROP FIELD T1Letter;

[#4]

So you're saying that since I can't specify a specific table name I should make a new table, give that field a different name, and and do the exists on that field? Not exactly perfect but still genius, thanks.