Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Bharathi09
Creator II
Creator II

Pulling certain data from excel in QS

Hi All,

 

I have excel sheet, with field name EMP(NAME/ID),

The data in some rows in that field be like 'Jake-twikdl0003', and in some rows data be like 'twikdl0003'

From both rows I need to pick 'kdl'.....how to pull this from two different types of rows?

Labels (2)
8 Replies
rthatcher
Contributor
Contributor

Hi,

Is the 'kdl####' always at the end of the cell, with the same amount of characters (3 letters followed by 4 numbers)?

If so, you could use RIGHT(field_name, 7) to select the last 7 digits.
Bharathi09
Creator II
Creator II
Author

[cid:227eb440-b787-4692-971e-c1fe3d22d117]
data is like this I need to pull 'kdl' from each row
Answer pls

Thanks in advance
menta
Partner - Creator II
Partner - Creator II

You can try with index('kdl') if this values is >0 the string is in the record

vinieme12
Champion III
Champion III

Option1, using index()  ,

using lower as index is case sensitive   but will match the search string in any position of the string

 

Where  Index( lower(EmpName),'kdl')   ;  

 

 

Option2, using Wildmatch()  ,  case insensitive but will only find search string as per position in string

Where  Wildmatch( EmpName,'*kdl*')   ;    // finds kdl in the middle of the string

Where  Wildmatch( EmpName,'kdl*')   ;    // finds kdl in the beginning of the string

Where  Wildmatch( EmpName,'*kdl')   ;    // finds kdl in the end of the string

Where  Wildmatch( EmpName,'kdl*','*kdl*','*kdl')   ;    // finds kdl in all above scenarios 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Bharathi09
Creator II
Creator II
Author

How to pull 10th to 12th letters in any row?
examole: empid-ssdkdl223
like 'kdl' I need to pull 10th to 12th characters from data

Please let me know
Thanks in advance
Bharathi09
Creator II
Creator II
Author

Some rows are like  empid-ssdkdl223

some rows are like ssdkdl223

from every row I need to pull 3 letters  after ssd...how to do it??

vinieme12
Champion III
Champion III

Use mid() function

Mid(empname,10,12)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Vegar
MVP
MVP

As @vinieme12 mentioned,  the index function can be useful in this case. I'm combining  it with mid() which is also mentioned.

mid( [EMP(NAME/ID)], Index( lower( [EMP(NAME/ID)] ),'ssd')   + 3, 3)  

This will pull the first three letter after the first occurrence of the string 'ssd' in the field [EMP(NAME/ID)].