topic Re: Check if a field value is in all the posibilities of other field. in QlikView

Check if a field value is in all the posibilities of other field.

gines_rueda — Fri, 04 Oct 2013 15:14:30 GMT

Hello All,

How can I do it to have something like this:

I have two tables.

Table 1:

Id1,

V1.

Table 2;

Id1,

V2.

I want a expresion that for the second table return me the rows in Table 2 where V2 is in the set of availables V1.

(V1 and V2 are same typo of data but I don't want a relation).

Something like this:

= If ( V2 IN (V1), Id1);

Re: Check if a field value is in all the posibilities of other field.

Fri, 04 Oct 2013 15:25:14 GMT

Hi,

I would do sth like:

if(mixmatch(concat({1} V1, '|') & '|', V2& '|')>0, id1)

I create a character set with concat. If V2 is in this set (I add a pipe to be sure that there is no inclusion of text that would return false values, mixmatch returns sth > 0.

{1} to get all values

Fabrice

Re: Check if a field value is in all the posibilities of other field.

gines_rueda — Fri, 04 Oct 2013 16:38:00 GMT

Hi,

Thanks for answering.

This approach is not working for me, seems that mixmatch is not returning results.

I think there is a problem with the level of aggregation in the expression because the first part of the mixmatch return me the string with all the values and the second part return me the items individually. But then the mixmatch does not return > 0.

What do you think?

Ginés.

Re: Check if a field value is in all the posibilities of other field.

Fri, 04 Oct 2013 20:08:44 GMT

Hi,

I used this formula to set a backround color for the selected companies :

if (match( '|' & Company & '|', '|' & concat({1} Company, '|' ) & '|' ) , rgb(141,170,203),rgb(252,115,98))

The first parameter is the string we are searching, the second is the string we are looking in it (it is why I put {1} to get all companies. So effectively, we need perhaps to inverse the arguments:

= if(match('|' & V2 & '|', '|' & concat({1} V1, '|') & '|') > 0, id1)

where V2 and V1 are different fields having the same values

Fabrice

Re: Check if a field value is in all the posibilities of other field.

gines_rueda — Tue, 08 Oct 2013 08:19:43 GMT

Hello again Fabrice,

I have tried all the possibilities with the function you have gave here, but nothing, "match" func and his variants do not return anything.

I have check and the concatenated string is created correctly, also the item to check, but seems that the expression does not work with that.

Regards.

Ginés.

Re: Check if a field value is in all the posibilities of other field.

CELAMBARASAN — Tue, 08 Oct 2013 08:31:16 GMT

Try something like

= If (MixMatch(V2 ,$(=Concat(V1, Chr(39) & ',' & Chr(39)))), Id1)

Re: Check if a field value is in all the posibilities of other field.

Gysbert_Wassenaar — Tue, 08 Oct 2013 08:36:10 GMT

See attached example