topic Match Function in QlikView

Match Function

Thu, 17 Jun 2010 07:59:19 GMT

Hi All,

I have a Table which has a Code column and it has suppose 100 values. But using Match function I want to bring all of them and I dont want to write them in Match function,

Suppose what we do in mactch is:

Load

If ( Match(Code, 'A', 'B','C','D','E','F','G','H')) AS XYZ,

Resident Table1;

Can I use the Table in side the Match function instead of Value i.e A,B,C?

For Example:

Load

If ( Match(Code, LOAD STATEMENT)) as XYZ,

Resident Table1;

Is it possible if not so is there any other way for doing the same thing,

Regards,

Khalid

Match Function

sparur — Thu, 17 Jun 2010 08:12:38 GMT

Hello, Khalid.

may be concat() function is useful.

If values 'A', 'B','C','D','E','F','G','H' ... are stored in other field or table you can use concat(), for example:

tmpValues:

LOAD

concat(Value) as FullListOfValues

FROM...

where field Value has values are: 'A', 'B','C','D','E','F','G','H'

After this you get list of your values

and then use it in match:

Load

If ( Match(Code, FiledValue('FullListOfValues', 1))) AS XYZ,

Resident Table1;

Match Function

Thu, 17 Jun 2010 08:54:20 GMT

Hi Anatoly,

Its so nice of you that you given a detailed example for my proble, so nice of you. I am showing you my table and how did in it what you said. See if there is any error, its not executing i dont know where the problem lies. The error after reload is as follows:

///Error in expression:
FILEDVALUE is not a valid function

CUSTOMERS2:

Load

CUST_CITY as City,
CUST_STATE as State,
CUST_OLD_CODE as Code,
CUST_COUNTRY as Country,
concat(Code) as FullListOfCodes,
If ( Match(Code, FiledValue('FullListOfValues', 1))) AS XYZ

Resident CUSTOMERS ///

CUSTOMERS:

LOAD

CUST_BUS_UNIT,

CUST_CUSTOMER

AS KEY_CUSTOMER,

CUST_CUSTOMER

AS KEY_SALESPERSON,

CUST_NAME1

CUST_NAME2

CUST_STATUS

CUST_CUST_TYPE

AS KEY_CUSTTYPE,

CUST_CREDIT_MANAGER

CUST_CREDIT_LIMIT

CUST_TAX_AUTHORITY

CUST_TAX_EXEMPT_ID

CUST_STR_ADDR1

CUST_STR_ADDR2

CUST_STR_ADDR3

CUST_CITY

CUST_STATE

CUST_POSTAL_CODE

CUST_COUNTRY

CUST_PHONE1

CUST_PHONE2

CUST_FAX1

CUST_FAX2

CUST_E_MAIL1

CUST_E_MAIL2

CUST_EDI1

CUST_EDI2

CUST_CRE_BY

CUST_CRE_DATE

CUST_NOTES

CUST_STAX_NO

CUST_NTAX_NO

CUST_CATEGORY

CUST_STAX_STATUS

CUST_NATURE

CUST_CREDIT_LIMIT2

CUST_DUTY_AUTHORITY

CUST_COD_INV

CUST_PARENT

CUST_CUSTOMER_PARENT

CUST_PARENT_CODE

CUST_OLD_CODE

FROM

(

qvd)

;

CUSTOMERS2:

Load

CUST_CITY

as City,

CUST_STATE

as State,

CUST_OLD_CODE

as Code,

CUST_COUNTRY

as Country,

concat

(Code) as FullListOfCodes,

If( Match(Code, FiledValue('FullListOfValues', 1))) AS XYZ

Resident

CUSTOMERS;

Match Function

Thu, 17 Jun 2010 08:56:04 GMT

Ooops, copy paste makes problem. let me try again.

Match Function

Thu, 17 Jun 2010 08:58:18 GMT

CUSTOMERS:

LOAD CUST_BUS_UNIT,

CUST_CUSTOMER AS KEY_CUSTOMER,

CUST_CUSTOMER AS KEY_SALESPERSON,

CUST_NAME1,

CUST_NAME2,

CUST_STATUS,

CUST_CUST_TYPE AS KEY_CUSTTYPE,

CUST_CREDIT_MANAGER,

CUST_CREDIT_LIMIT,

CUST_TAX_AUTHORITY,

CUST_TAX_EXEMPT_ID,

CUST_STR_ADDR1,

CUST_STR_ADDR2,

CUST_STR_ADDR3,

CUST_CITY,

CUST_STATE,

CUST_POSTAL_CODE,

CUST_COUNTRY,

CUST_PHONE1,

CUST_PHONE2,

CUST_FAX1,

CUST_FAX2,

CUST_E_MAIL1,

CUST_E_MAIL2,

CUST_EDI1,

CUST_EDI2,

CUST_CRE_BY,

CUST_CRE_DATE,

CUST_NOTES,

CUST_STAX_NO,

CUST_NTAX_NO,

CUST_CATEGORY,

CUST_STAX_STATUS,

CUST_NATURE,

CUST_CREDIT_LIMIT2,

CUST_DUTY_AUTHORITY,

CUST_COD_INV,

CUST_PARENT,

CUST_CUSTOMER_PARENT,

CUST_PARENT_CODE,

CUST_OLD_CODE

FROM

(qvd);

CUSTOMERS2:

Load

CUST_CITY as City,

CUST_STATE as State,

CUST_OLD_CODE as Code,

CUST_COUNTRY as Country,

concat(Code) as FullListOfCodes,

If ( Match(Code, FiledValue('FullListOfValues', 1))) AS XYZ

Resident CUSTOMERS;

Match Function

Thu, 17 Jun 2010 09:00:16 GMT

Error is:

///Error in expression:
FILEDVALUE is not a valid function

CUSTOMERS2:

Load

CUST_CITY as City,
CUST_STATE as State,
CUST_OLD_CODE as Code,
CUST_COUNTRY as Country,
concat(Code) as FullListOfCodes,
If ( Match(Code, FiledValue('FullListOfValues', 1))) AS XYZ

Resident CUSTOMERS ///

Is fieldvalue a function.?

CUSTOMERS:

LOAD CUST_BUS_UNIT,

CUST_CUSTOMER AS KEY_CUSTOMER,

CUST_CUSTOMER AS KEY_SALESPERSON,

CUST_NAME1,

CUST_NAME2,

CUST_STATUS,

CUST_CUST_TYPE AS KEY_CUSTTYPE,

CUST_CREDIT_MANAGER,

CUST_CREDIT_LIMIT,

CUST_TAX_AUTHORITY,

CUST_TAX_EXEMPT_ID,

CUST_STR_ADDR1,

CUST_STR_ADDR2,

CUST_STR_ADDR3,

CUST_CITY,

CUST_STATE,

CUST_POSTAL_CODE,

CUST_COUNTRY,

CUST_PHONE1,

CUST_PHONE2,

CUST_FAX1,

CUST_FAX2,

CUST_E_MAIL1,

CUST_E_MAIL2,

CUST_EDI1,

CUST_EDI2,

CUST_CRE_BY,

CUST_CRE_DATE,

CUST_NOTES,

CUST_STAX_NO,

CUST_NTAX_NO,

CUST_CATEGORY,

CUST_STAX_STATUS,

CUST_NATURE,

CUST_CREDIT_LIMIT2,

CUST_DUTY_AUTHORITY,

CUST_COD_INV,

CUST_PARENT,

CUST_CUSTOMER_PARENT,

CUST_PARENT_CODE,

CUST_OLD_CODE

FROM

(qvd);

CUSTOMERS2:

Load

CUST_CITY as City,

CUST_STATE as State,

CUST_OLD_CODE as Code,

CUST_COUNTRY as Country,

concat(Code) as FullListOfCodes,

If ( Match(Code, FiledValue('FullListOfValues', 1))) AS XYZ

Resident CUSTOMERS;

Best Regards,

Khalid

Match Function

sparur — Thu, 17 Jun 2010 09:09:56 GMT

sorry for my mistake.

fieldValue not a FiledValue 🙂

Match Function

Thu, 17 Jun 2010 10:58:14 GMT

Hi,

I have a Table of CITY , When I go for match function I had to write some times 4 cities names, some times 6 cities name, what I am facing is I write again and again city names regarding different criterias, Is it possible that I bring a Field in the Match fucntion in which that particular fields contains all the city name and should match them on conditions, as such there would be no need for writng city names again and again. I mean I dont want to write city names in Match function again and again I just need a Field which should fetch city names and go for matching in Match function. For example it takes log if I have to write 25 city names, is there any simple solution that city be fetched in Match function without writing them again and again.

CITY:

London

New York

Maxico

and so on all the cities upto 100.

if (Match (City, 'London', 'New York', 'Maxico','Karachi','Mumbai')) as XYZ

in upper case I had to write all the names depending condition.

is it possible like: IF (MATCH(City)) as XYZ.

Regards,

Khalid

Match Function

sparur — Thu, 17 Jun 2010 11:10:31 GMT

I can't understand what you want to achieve.

Can you post your qvw or more detail task ?

Match Function

johnw — Fri, 18 Jun 2010 01:45:16 GMT

Khalid, this is the fourth thread I've found where you asked the same question. Was that really necessary? See my seven different answers in the thread below.

http://community.qlik.com/forums/p/27167/117203.aspx#117203