topic Re: Extract number from text field in QlikView

Extract number from text field

Fri, 28 Jun 2013 23:55:18 GMT

hi in the following excel applikation i wish to extract only the last four numbers off each row.

if a row is lacking the four numbers (companycode) i wish not to read them.

i have tried trim function without luck as each of the rows have different lengths;

any suggestions?

Best,

Brad

Gysbert_Wassenaar — Sat, 29 Jun 2013 07:02:38 GMT

I suggest you attach the excel file.

Sat, 29 Jun 2013 11:05:08 GMT

Thanks i have attached the excel sheet

swuehl — Sat, 29 Jun 2013 11:26:36 GMT

Try something like

Company:

LOAD

Company,

if(isnum(right(Company,4)),right(Company,4)) as Company_num,

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1);

or if you only want to read these companies:

Company:

LOAD

Company,

right(Company,4) as Company_num

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1)

where isnum(right(Company,4));