Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

Var1Var2DataFlag
2000012-1720
7000042-1251
7000062-1570
7000022-130
2000015-13450
7000007-13250
700002863210
70000296320
7000062121541
7000062311091
700006212861
-16630
-112271
-1436430
70000239340
700006492341
4596731

1 Solution

Accepted Solutions

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

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

6 Replies
Not applicable

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

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!

Not applicable

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

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

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

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

Not applicable

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

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

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

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

Not applicable

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

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