Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andymanu
Creator II
Creator II

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
Chanty4u
MVP
MVP

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

Qlik Community Tip: Marking Replies as Correct or Helpful

View solution in original post

11 Replies
Chanty4u
MVP
MVP

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
Creator II
Creator II
Author

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
Partner
Partner

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
andymanu
Creator II
Creator II
Author

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
MVP
MVP

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Wow its so cool and get the job done.

Thank you very much.

Chanty4u
MVP
MVP

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

Qlik Community Tip: Marking Replies as Correct or Helpful

View solution in original post

andymanu
Creator II
Creator II
Author

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