Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
adrianrischbeck
New Contributor

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

Re: Upload from Excel without leading zero

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

Re: Upload from Excel without leading zero

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

adrianrischbeck
New Contributor

Re: Upload from Excel without leading zero

Excellent. This works fine Thanks for your help Felip.

Partner
Partner

Re: Upload from Excel without leading zero

No problem, glad it helped .