Selecting alphabetical letters from a mix of letters and numbers

[andymanu]

Hi,

I got a filed named "ITEM_CODE" which got both numbers and alphabetic letters in it.

I need to filter the data rows which only got letters.

Please find a sample data set load code below,

Table:

Load * Inline [

Item_Code, Item_Name

CBDGHD, A

124567,C

DEFAFD,D

243671,E

147568, H

ABDPOL,G

]

Appreciate if anyone could propose me a method to filter only the rows which got alphabet letter ( according to the scenario 3 instances) from the entire data set.

Awaiting for your reply.

Thanks in advance

Regards,

Andy

[Chanty4u]

try this

Table:

load *,

num(Item_Code) as ItemNum,

Text(Item_Code) as ItemText;

Load * Inline [

Item_Code, Item_Name

CBDGHD, A

124567,C

DEFAFD,D

243671,E

147568, H

ABDPOL,G

]

;

exit script;

[andymanu]

Hi,

Found a method using "KeepChar" function.

Ex," Keepchar(Item_Code, '0123456789') as Item_Number"

Since I am working on a large data set (which got more than 100,000 records), will the above function works efficiently to resolve my issue specified above?

Thanks in advance

Regards,

Andrew

[jonathandienst]

Like this to exclude all strings that can be converted to numbers (all digits):

Table2:

NoConcatenate

LOAD * Resident Table

Where Alt(Item_Code, 0) = 0;

Drop Table Table;

Or like this to exclude that that contain any digits:

Where Len(KeepChar(Item_Code, '0123456789') = 0;

[andymanu]

Hi,

Thanks for your code.

Its working but the concern is "ItemText" field will have all the data rows.

If that could be separated, it would be great.

Thanks

Andrew

[Chanty4u]

try this

Table:

load *,

num(Item_Code) as ItemNum,

Text(PurgeChar(Item_Code,' 01234567890')) as ItemText;

Load * Inline [

Item_Code, Item_Name

CBDGHD, A

124567,C

DEFAFD,D

243671,E

147568, H

ABDPOL,G

]

;

exit script;

[andymanu]

Hi Jonathan,

You are of course correct and it's working fine. However, the issue with the WHERE clause is it drops the data rows (ex - rows with either numbers or the letters depending on which where clause you use) and if later required to calculate the filtered data may be as a percentage of the total number, you won't be able to do it.

Hope I am not incorrect.

Thank you very much.

Regards,

Andy

[andymanu]

Wow its so cool and get the job done.

Thank you very much.

[Chanty4u · Accepted solution]

Nice   please close the thread by making correct and helpful answers from the above

Qlik Community Tip: Marking Replies as Correct or Helpful

[andymanu]

Hi All,

Thank you all very much for the prompt and valuable support provided.

I  was able resolve my issue. However, got another issue to be resolved and it should be an easy work for you all and would be greatly appreciated if you could help me out.

I have copied the respective link to the above said issue below,

Applying Flags

Link is, "https://community.qlik.com/thread/305272?sr=inbox"

It would be a great help if you could provide the steps to accomplish the above issue (Please refer the latter part of the discussion).

I got no idea whether i have written the expression at the correct location.

Thanks in advance

regards,

Andy