topic Re: Finding values from one column in other column in QlikView

Finding values from one column in other column

mlarruda — Sat, 16 Nov 2024 01:56:27 GMT

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.

Re: Finding values from one column in other column

y_grynechko — Thu, 14 Nov 2019 21:01:45 GMT

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:

Re: Finding values from one column in other column

syedabik — Fri, 15 Nov 2019 03:09:19 GMT

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

Re: Finding values from one column in other column

mlarruda — Mon, 18 Nov 2019 13:00:13 GMT

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?

Re: Finding values from one column in other column

mlarruda — Mon, 18 Nov 2019 20:39:04 GMT

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?

Re: Finding values from one column in other column

syedabik — Tue, 19 Nov 2019 02:21:50 GMT

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;

Re: Finding values from one column in other column

Brett_Bleess — Tue, 19 Nov 2019 16:39:08 GMT

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

Re: Finding values from one column in other column

mlarruda — Fri, 22 Nov 2019 14:01:12 GMT

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!

Re: Finding values from one column in other column

Arthur_Fong — Tue, 03 Dec 2019 01:17:48 GMT

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

Re: Finding values from one column in other column

mlarruda — Tue, 03 Dec 2019 14:44:54 GMT

Worked! Thank you very much!