topic Upload from Excel without leading zero in QlikView

Upload from Excel without leading zero

Anonymous — Tue, 04 Sep 2018 11:09:29 GMT

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

Re: Upload from Excel without leading zero

felipedl — Tue, 04 Sep 2018 17:27:52 GMT

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.

Re: Upload from Excel without leading zero

Anonymous — Wed, 05 Sep 2018 07:50:52 GMT

Excellent. This works fine Thanks for your help Felip.

Re: Upload from Excel without leading zero

felipedl — Wed, 05 Sep 2018 16:46:46 GMT

No problem, glad it helped .