Advanced Search - search in two variables in one formula (OR)

[#1]

Hi,

I have a situation where I have to make a search on two variables - I need the data where Var1 values are in list of specific values (so match function I suppose) or Var2 values are in list of some specific values. What I think I need to do but don't know how is to create an advanced search which would do a "search in" for two different dimensions at the same time (for the OR statement).

So far I've tried to apply the advanced filter, used the Var1 in the "search in" and the below as the code (these are dimensions, the values are IDs):

=(Match([Var1],'41','45','39','7000064','7000042') OR Match([Var2],'12','31','32','49','51'))

but this doesn't work correctly. I cannot create any new variables and I don't have one variable on which I could do the "search in". You can assume that all I have is something like a table I've attached (besides the flag variable).

Thanks in advance for your help,

Marcin

Var1	Var2	Data	Flag
2000012	-1	72	0
7000042	-1	25	1
7000062	-1	57	0
7000022	-1	3	0
2000015	-1	345	0
7000007	-1	325	0
7000028	6	321	0
7000029	6	32	0
7000062	12	154	1
7000062	31	109	1
7000062	12	86	1
-1	6	63	0
-1	12	27	1
-1	43	643	0
7000023	9	34	0
7000064	9	234	1
45	9	673	1

[#2]

I am slightly confused by what you are aiming to do...

If you want one variable in which to search you could, in your script, try Var1 & Var2 as VarCombined then search for parts of VarCombined you are looking for.

But otherwise the statement you have above should work...For example in your script you could include:

Where

(Wildmatch([Var1],'41','45','39','7000064','7000042') OR Wildmatch([Var2],'12','31','32','49','51'))

Would give you all results where Var1 or Var2 matched the parameters listed.

Otherwise are you looking to "search in" like a regular expression because that can be solved by

using wildmatch but using the * rather than the % in SQL. e.g. wildmatch([Var1],'70*') would give you all results starting in 70 with anything after it.

Hope this helps!

[#3]

Hi,

Well, I could do the things you mention but I don't have the access to the script (cannot edit it) and are not allowed to create any new variables so VarCombined is off limits as well. I've tried to filter on '1' in:

=if(Match([Var1],'41','45','39','7000064','7000042') OR Match([Var2],'12','31','32','49','51')),1,0)

but this doesn't work properly as well, as I think that the formula would have to be defined as a new variable for me to be able to filter on it.

Thanks,

Marcin

[CELAMBARASAN]

Hi,

     It will work.Wat result it shows now?

=if(Match([Var1],'41','45','39','7000064','7000042') OR Match([Var2],'12','31','32','49','51'),1,0)

Celambarasan

[#5]

Hi,

When I hit on '1' in the table it gives me just the line I've hit i.e. if Var1= 41 and Var2=10 then it will apply those two filters to the report.

Thanks,

Marcin

[CELAMBARASAN · Accepted solution]

Hi,

     That was the qlikview functionality.

     If you need all the ones then have a list box with this expression.From there you select 1 and 0 then it will list all the ones and zero's.

Celambarasan

[#7]

Celambarasan,

I think this actually works, I've tried that before but I've added the formula in the expressions tab which caused the list box to be empty. Now I've put it as <expression> in the fields section "General" tab and it seems to work fine.

Thank you very much,

Marcin