topic Pulling certain data from excel in QS in Visualization and Usability

Pulling certain data from excel in QS

Bharathi09 — Mon, 19 Dec 2022 14:11:01 GMT

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?

Re: Pulling certain data from excel in QS

rthatcher — Mon, 19 Dec 2022 14:20:06 GMT

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.

Re: Pulling certain data from excel in QS

Bharathi09 — Mon, 19 Dec 2022 14:27:36 GMT

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

Thanks in advance

Re: Pulling certain data from excel in QS

menta — Mon, 19 Dec 2022 15:24:52 GMT

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

Re: Pulling certain data from excel in QS

vinieme12 — Tue, 20 Dec 2022 02:49:09 GMT

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

Re: Pulling certain data from excel in QS

Bharathi09 — Tue, 20 Dec 2022 05:19:36 GMT

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

Re: Pulling certain data from excel in QS

Bharathi09 — Tue, 20 Dec 2022 05:24:51 GMT

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

Re: Pulling certain data from excel in QS

vinieme12 — Tue, 20 Dec 2022 05:31:37 GMT

Use mid() function

Mid(empname,10,12)

Re: Pulling certain data from excel in QS

Vegar — Tue, 20 Dec 2022 05:47:16 GMT

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)].