Match Function

[#1]

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

[sparur]

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;

[#3]

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;

[#4]

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

[#5]

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;

[#6]

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

[sparur]

sorry for my mistake.

fieldValue not a FiledValue 🙂

[#8]

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

[sparur]

I can't understand what you want to achieve.

Can you post your qvw or more detail task ?

[johnw]

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