Filter Data

[andymanu]

Hi,

I got a table which looks like below,

Item_Code	Item_Name
CBDG3CD	A
124567	D
DEFAFD	E
243671	G
ABD256	H
AGHDK2	N
BCDWH5	M

I want to filter the data based on the below requirements;

1. Select the last two characters of the Item_Code. (I may use the Right() function )

2. I want to Flag/Filter the Item_Codes which ONLY got alphabetic letter. Ex- the last two codes cannot have a number.

The out put should be something like below,

Item_Code	Item_Name	Flag
CBDG3CD	A	1
124567	D	0
DEFAFD	E	1
243671	G	0
ABD256	H	0
AGHDK2	N	0
BCDWH5	M	0

Could anyone help me to accomplish the above task would be greatly appreciated.

Regards,

Andy

[sunny_talwar · Accepted solution]

May be this

If(Len(PurgeChar(Right(Item_Code, 2), '0123456789')) = 2, 1, 0) as Flag

[andymanu]

Hi Sunny,

Thank you so much. Yes it worked.

I was bit struggling to combine the Right function and the if condition together as a single combined function.

Regards,

Andy

[thi_pham]

Isnum function can help this case: (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/LogicalFunctio...)

     if(isnum(right(Item_Code,2)) = -1, 0, 1)

I think it works well even in case length of Item_Code < 2.