Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mlarruda
Creator
Creator

Finding values from one column in other column

Dear friends,

Suppose I have three columns like this:

NAMEVALUE1VALUE2
AAA11
BBB21
CCC22
DDD13
EEE32
FFF41
GGG35


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:

NAMEVALUE1VALUE2FLAG
AAA11yes
BBB21yes
CCC22yes
DDD13yes
EEE32yes
FFF41no
GGG35yes


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.

Labels (1)
1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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())

MC.PNG

View solution in original post

9 Replies
y_grynechko
Creator III
Creator III

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:

 

Capture.PNG

syedabik
Partner - Contributor II
Partner - Contributor II

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);

Capture.PNG

mlarruda
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner - Contributor II
Partner - Contributor II

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;

Capture.PNG

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
mlarruda
Creator
Creator
Author

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
Partner - Specialist III
Partner - Specialist III

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())

MC.PNG

mlarruda
Creator
Creator
Author

Worked! Thank you very much!