Using IN clause in QlikView

In SQL statements, it is very useful to use IN clause in the query to compare your value against the provided list of values .

In SQL you can write a query such as

Select ProductName,Revenue from Products

where ProductName IN (‘Chairs’,’Tables’,’CrossArmChairs’);

QlikView does not have IN  operator instead QlikView provide match() function to achieve this functionality

I will load an Inline table

Product_Inline

Now to load a table with ProductName IN  (‘Chairs’,’Tables’,’ArmChairs’) you will use match()

match

match() does case sensitive comparision. It will return 1 if ProductName = Chairs but will return 0 if ProductName = chairs

For case insenstive comparison, you can use mixmatch()

wildmatch() helps in case insensitive comparison and also permits the use of wild characters

WildMatch

You can use Not preceding Match()  to get the ProductNames that are NOT IN the list of values

To load ProductNames which are not ‘Boats’ , you can write

NotMatch

Match() can be used in an expression while creating a chart.

If_Match

To learn about using Match function to group dimensions in Qlik Sense, visit http://www.learnallbi.com/how-to-create-master-items-in-qlik-sense/

3 thoughts on “Using IN clause in QlikView

    • Sudhakar:
      You can do limited load to load only 100 records.
      In the script editor, click on debug and put your desired number of records under Limited Load.

  1. How to merge information from different sources.
    For example:
    After loading a CSV data, enrich it with specific data from an SQL datasource.

    [NOVEMBRO]:
    LOAD [carteira],
    [contrato],
    [valor],
    [cpf] as uniqueID,
    [score],
    FROM [lib://Desktop/NOVEMBRO.csv]
    (txt, utf8, embedded labels, delimiter is ‘,’, msq);

    LIB CONNECT TO [Microsoft_SQL_Server_PDW];
    [pfDadosCadastrais]:
    SELECT CPF,
    Sexo,
    Idade
    FROM “Pessoa_Fisica”.dbo.pfDadosCadastrais
    WHERE CPF=uniqueID;

Leave a Reply

Your email address will not be published. Required fields are marked *