Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Upload from Excel without leading zero

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

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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
50ET/WKM/050
58ET/WKM/058
58ET/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.

View solution in original post

3 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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
50ET/WKM/050
58ET/WKM/058
58ET/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
Not applicable
Author

Excellent. This works fine Thanks for your help Felip.

felipedl
Partner - Specialist III
Partner - Specialist III

No problem, glad it helped .