Upload from Excel without leading zero

[Anonymous]

Hello everyone,

I want to import data from excel via the  Data Editor. The field in Excel is in a standard format.. In Order to get the proper key in Qlik Sense I just need the 3 digits on the right side (right,3) but without leading zeros. So that for example  "ET/WKM/058" shows up like "58".

Thanks in advance,

Adrian

[felipedl · Accepted solution]

Hi Adrian,

Lets say you have this data:

load * Inline

[

Data

ET/WKM/058

ET/WKM/050

ET/WKM/58

]

If you do the following

load

num(subfield(Data,'/',SubStringCount(Data,'/')+1)) as Teste,

Data;

load * Inline

[

Data

ET/WKM/058

ET/WKM/050

ET/WKM/58

]

You`ll get

Teste	Data
50	ET/WKM/050
58	ET/WKM/058
58	ET/WKM/58

As an explanation of the formula

SubStringCount(Data,'/')+1): will get the last slash "/" position

subfield(Data,'/',SubStringCount(Data,'/')+1): will get the number part, by getting the last slash "/" caracter

num(subfield(Data,'/',SubStringCount(Data,'/')+1)): transform it into the number version, so 058 will get 58

hope it helps.

[Anonymous]

Excellent. This works fine Thanks for your help Felip.

[felipedl]

No problem, glad it helped .