Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andymanu
Contributor

Selecting alphabetical letters from a mix of letters and numbers

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

1 Solution

Accepted Solutions
sureshqv
Esteemed Contributor III

Re: Selecting alphabetical letters from a mix of letters and numbers

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

Qlik Community Tip: Marking Replies as Correct or Helpful

11 Replies
sureshqv
Esteemed Contributor III

Re: Selecting alphabetical letters from a mix of letters and numbers

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
Contributor

Re: Selecting alphabetical letters from a mix of letters and numbers

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

MVP
MVP

Re: Selecting alphabetical letters from a mix of letters and numbers

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
Contributor

Re: Selecting alphabetical letters from a mix of letters and numbers

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

sureshqv
Esteemed Contributor III

Re: Selecting alphabetical letters from a mix of letters and numbers

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
Contributor

Re: Selecting alphabetical letters from a mix of letters and numbers

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
Contributor

Re: Selecting alphabetical letters from a mix of letters and numbers

Wow its so cool and get the job done.

Thank you very much.

sureshqv
Esteemed Contributor III

Re: Selecting alphabetical letters from a mix of letters and numbers

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

Qlik Community Tip: Marking Replies as Correct or Helpful

andymanu
Contributor

Re: Selecting alphabetical letters from a mix of letters and numbers

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

Community Browser