Finding values from one column in other column

[mlarruda]

Dear friends,

Suppose I have three columns like this:

NAME	VALUE1	VALUE2
AAA	1	1
BBB	2	1
CCC	2	2
DDD	1	3
EEE	3	2
FFF	4	1
GGG	3	5

So, my goal is to know which NAMES has values from VALUE1 that also appear in VALUE2. I wish to have a result like this:

NAME	VALUE1	VALUE2	FLAG
AAA	1	1	yes
BBB	2	1	yes
CCC	2	2	yes
DDD	1	3	yes
EEE	3	2	yes
FFF	4	1	no
GGG	3	5	yes

Note that EEE's FLAG value needs to be NO, since his VALUE1 (4) does not exists in VALUE2 column,

Anyone can help me? many thanks in advance.

[y_grynechko]

Hey, I am a little lost with your notes but if you want to create the flag that is going to say 'yes' when the Value2 exists in Value1 and says 'no' when Value2 doesn't exist in Value1 you can do something like this: 

T1:
LOAD
NAME,
VALUE1,
VALUE2
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

T2:
Load
NAME,
'yes' as Flag
Resident T1
Where Exists(VALUE1,VALUE2);

Load
NAME,
'no' as Flag
Resident T1
Where not Exists(VALUE1,VALUE2);

Result:

[syedabik]

Hi,

I think the correct syntax should be like this:

Data:
LOAD * Inline
[
NAME, VALUE1, VALUE2
AAA, 1, 1
BBB, 2, 1
CCC, 2, 2
DDD, 1, 3
EEE, 3, 2
FFF, 4, 1
GGG, 3, 5
];

Exist:
Load
NAME,
'Yes' as Flag
Resident Data
Where Exists(VALUE2,VALUE1);

NotExist:
Load
NAME,
'No' as Flag
Resident Data
Where not Exists(VALUE2,VALUE1);

[mlarruda]

Hi. It is exactly what I need. Thank you very much!

But would it be possible to define this flag as a graphic dimension rather than in the LOAD statement?

[mlarruda]

In truth, I don't have such NAME column and I don't know if open a new question topic might be better than continue here. I think that explain here its better so anyone can see the suggestions above and simply do the required corrections.

Consider, for example, this loading script:

Tab1:

LOAD * INLINE
[VALUE1
1
2
3
21
22
23
24
25
26
27
];

Concatenate

LOAD * INLINE
[VALUE2
1
2
3
15
16
17
35];

Exist:
Load
'Yes' as Flag
Resident Tab1
Where Exists(VALUE2,VALUE1);

NotExist:
Load
'No' as Flag
Resident Tab1
Where not Exists(VALUE2,VALUE1);

I'm attaching the results I obtained:

• LIST1 is a list of VALUE1 values;
• LIST2 is a list of VALUE2 values;
• T1 is a table of VALUE1 and FLAG values;
• T2 is a table of VALUE2 and FLAG values;
• TABLE FLAG is a table (graphic) with FLAG as Dimension and COUNT(VALUE1) as Expression.

So, since only values 1, 2 and 3 appears in both VALUE1 and VALUE2 variables, I wanted that:

• T1 shows Yes for the values 1, 2 and 3 and No for the other values (I can't understand why both Yes and No are shown for all values);
• The same for T2;
• TABLE FLAG shows "No - 7" (since 7 values from VALUE1 doesn't exist in VALUE2) and "Yes - 3" (since 3 values from VALUE1 does exist in VALUE2).

So, anyone can help me?

[syedabik]

Hi,

Maybe you can consider this option also. 

Tab1:
Load *,
AutoNumber(VALUE1) as %KEY;
LOAD * INLINE
[VALUE1
1
2
3
21
22
23
24
25
26
27
];

Join
Load *,
AutoNumber(VALUE2) as %KEY;
LOAD * INLINE
[VALUE2
1
2
3
15
16
17
35
];

Final:
Load *,
If(VALUE1=VALUE2,'Yes','No') as Flag
Resident Tab1;

DROP Table Tab1;

[Brett_Bleess]

Marcelo, did Syed's post help you get what you needed?  If so, do not forget to come back to the thread and use the Accept as Solution button on that post to give credit for the help and let other Community Members know that worked.  If you are still working on things, leave an update as to what you still need at this point.

Regards,
Brett

[mlarruda]

Hi, Brett.

First of all, sorry for the silence. Last days I was involved in other jobs and only now could come back for this problem.

Secondly, my real databank is very complex and I'm afraid that this AUTONUMBER function can cause some problems to my variables. Also, I noted that the COUNT(Flag) table in Syed's solution is summing 14 and I wanted it summed 10 (since VALUE1 has only 10 values).

Now, I tried this script and obtained the attached result. Except for the repeated rows with hyphens, it is working well.

Tab1:

LOAD * INLINE
[VALUE1
1
2
3
21
22
23
24
25
26
27
];

Concatenate

LOAD * INLINE
[VALUE2
1
2
3
15
16
17
35];

Concatenate

ExistFlag:
Load VALUE1, VALUE2,
If(Exists(VALUE2,VALUE1),'Yes','No') as Flag
Resident Tab1

So, if anyone can work in this script to exclude these hyphens, this would be great!

[Arthur_Fong · Accepted solution]

Try using the calculated dimensions and expression highlighted:

Expression:

=count(if(not isnull(Flag),VALUE1,null()))

Calculated dimension:

VALUE1 :

=if(not isnull(Flag),VALUE1,null())

Flag:

=if(not isnull(VALUE1),Flag,null())

[mlarruda]

Worked! Thank you very much!