Need Help in Creating a Filter

[#1]

Hello Friends,

I have a single table and as I need to create a Filter like "FLAG YES/NO", I have column name FLAG value is like A,D,X,T and One more Column like CODES it contains the value like null,Z,H,U,I.

As I need to create a expression in the script based on the following conditions.

The condition is like if FLAG value is "A","D" and the CODE of "Z","H","U" and "I" means  "Flag Yes"

the other condition is if the FLAG value is "A", "D" and the CODE is "NULL" means the "Flag NO"

As I tried like if((FLAG='A','D') and (CODES<> Null),'FLAG YES'), If((FLAG='A','D') and (CODES=Null),'FLAG No') as [FLAG YES/NO], but its not working.

Can any one help me out from this...

Regards

Chriss

[sivarajs]

if(wildmatch(FLAG,'A','D') and wildmatch(CODE,'Z','H','U','I'),'Flag Yes',if(wildmatch(FLAG,'A','D') and isnull(CODE),'Flag No'))

[jonathandienst]

Chriss

This will return Null if none of the conditions are met:

If(Match(FLAG, 'A', 'D'), If(Match(CODES, 'Z', 'H', 'U', 'I'), 'Flag Yes', If(IsNull(CODES), 'Flag No')))

This will return 'UNKNOWN' if none of the conditions are met:

If(Match(FLAG, 'A', 'D') And Match(CODES, 'Z', 'H', 'U', 'I'), 'Flag Yes', If(Match(FLAG, 'A', 'D') And IsNull(CODES), 'Flag No', 'UNKNOWN'))

Hope that helps

Jonathan

Edit: Fixed syntax error in second expression

[#4]

Hello Sivaraj and Jonathan,

Thanks for your valuable reply, but still i have not achieved what I have requested.

1. The first portion of the expression If(Match(FLAG, 'A', 'D'), If(Match(CODES, 'Z', 'H', 'U', 'I'), 'Flag Yes'', this is working fine but the second one gives the worng result.

2. As I want this result in a single expression like if I add "FLAG YES/NO" in the list box, when i click either the "Flag Yes" means it has to show condtion (a) or if I press the "Flag No" means condition (b)

     a.  Match(FLAG, 'A', 'D') and Code('Z', 'H', 'U', 'I')

     b. Match(FLAG, 'A', 'D') and isnull(Code).

How to achieve this in a single exprssion in the script.

Regards

Chriss

[jonathandienst]

Chriss

It sounds like you need a field containing Flag Yes/Flag No. This you can put in a list box.

Then in your load script, you would associate the new field (lets call it FlagYN), something like this when you load the table containing FLAG and CODES:

LOAD

     .....

     FLAG,

     CODES,

    If(Match(FLAG, 'A', 'D'), If(Match(CODES, 'Z', 'H', 'U', 'I'), 'Flag Yes', If(IsNull(CODES), 'Flag No'))) As FlagYN,

     ...

FROM ...

Now you can put FlagYN in a list box and selecting a value will select the associated records

Hope that helps

Jonathan

[#6]

Hello Jonathan,

thanks again, I heard about a flag but to be honest I don't know how its works, could you please brief me.

and currently i am using only one table and using concatenate two tables merged together and I can split the expressions like as in the below.

Please advise If i am wrong....

FactTable:

CusotmerNumber,

CustomerName,

ProductCode,

ProductName,

....................

....................

Flag,

Codes,

If(Match(FLAG, 'A', 'D') and Code<> '' ,'Flag Yes') as FlagYN,

Qty,

Weight

from Test.qvd(QVD)

Concatenate FactTable

FactTable1:

CusotmerNumber,

CustomerName,

ProductCode,

ProductName,

....................

....................

Flag,

Codes,

If(Match(FLAG, 'A', 'D' and IsNull(CODES), 'Flag No')) As FlagYN,

Qty,

Weight

from Test.qvd(QVD)

Regards

Chriss

[jonathandienst]

Chriss

Why are you loading the qvd twice? - this will simply duplicate all the records and apply half the flag condition to each half.

You can do this load once like this:

FactTable:

     CustomerNumber,

     CustomerName,

     ProductCode,

     ProductName,

     ....................

     ...................

     Flag,

     Codes,

     If(Match(Flag, 'A', 'D'), If(Match(Codes, 'Z', 'H', 'U', 'I'), 'Flag Yes', If(IsNull(Codes), 'Flag No'))) As FlagYN,

     Qty,

     Weight

From Test.qvd(QVD);

One other point - field names are case sensitive - Flag is a different field to FLAG.

Regards

Jonathan