topic Re: Why zeros are added at start in QlikView

Why zeros are added at start

Thu, 10 Apr 2014 09:17:41 GMT

i have different form of medical Numbers ( '2226' , 'MQ234' )

But when i do a concat code such as Id & '-' & [Medical Number] the format i receive for the ones 2226 is 0000 2226

how can i make it not do that ???

Re: Why zeros are added at start

its_anandrjs — Thu, 10 Apr 2014 09:21:19 GMT

Use like

Trim(id) & '-' & [Medical Number]

if you have sample in excel then provide.

and also check that your field id contains only 2226 not 0000 2226 in the field.

Re: Why zeros are added at start

Thu, 10 Apr 2014 09:37:33 GMT

post some sample data..

Re: Why zeros are added at start

fkeuroglian — Thu, 10 Apr 2014 10:57:53 GMT

try this

REPLACE(LTRIM(REPLACE([Medical Number], '0', ' ')), ' ', '0')as [Medical Number],

Good luck

Fernando

Re: Why zeros are added at start

sunilkumarqv — Thu, 10 Apr 2014 11:10:53 GMT

Mixed of string and int better to be use Dual Function

dual( 'Id' , 'Medical Number' ) AS YouField

Re: Why zeros are added at start

rajat2392 — Thu, 10 Apr 2014 12:09:43 GMT

use the below code

concat([Medical Number] ,'-')

Re: Why zeros are added at start

Sat, 12 Apr 2014 10:57:40 GMT

DEar All Iam getting the following table

Medical Record NO.	FilterID
mq00937178	7708-mq00937178-01.17.2013
mq00949291	7708-mq00949291-01.23.2013
mq00969069	7708-mq00969069-12.19.2012
MQ00969106	7708-MQ00969106-12.19.2012
MQ00969309	7708-MQ00969309-12.20.2012
509	7708-00000509-04.25.2013
509	7708-00000509-06.20.2013
598	7708-00000598-03.20.2013
602	7708-00000602-04.25.2013

where i use in the script the following formula

LocationID & '-' & [Medical Record No. :] & '-' & Date as FilterID,

I tried all the solutions mentioned but didnt get

Re: Why zeros are added at start

mambi — Sat, 12 Apr 2014 11:49:05 GMT

can you provide us a sample data !

Re: Why zeros are added at start

maxgro — Sat, 12 Apr 2014 12:00:30 GMT

try with

LocationID & '-' & text([Medical Record No. :]) & '-' & Date as FilterID,

Re: Why zeros are added at start

its_anandrjs — Sat, 12 Apr 2014 13:08:14 GMT

If medical record no which is 00000509 and so with same length like 8 then use below code to make it NUM and then use in the script

LocationID & '-' & [Medical Record No. :] & '-' & Date as FilterID,

Try This

if(len([Medical Record NO.])=8,Num([Medical Record NO.]),[Medical Record NO.]) as [Medical Record NO.]

Re: Why zeros are added at start

its_anandrjs — Sat, 12 Apr 2014 13:17:45 GMT

Nothing change just try the below code with current script that yo use for concat

For this

LocationID & '-' & [Medical Record No. :] & '-' & Date as FilterID,

Try This if length is 8

LocationID & '-' & if(len([Medical Record No. :])=8,NUM([Medical Record No. :]),[Medical Record No. :]) & '-' & Date as FilterID,

Regards

Re: Why zeros are added at start

arsal_90 — Sat, 12 Apr 2014 13:18:12 GMT

You can use text() function to handle this situation

Re: Why zeros are added at start

marychen — Fri, 26 May 2017 23:05:08 GMT

QlikView randomly added a leading zero to one of the text fields. This solution works for my situation. Thanks!

Re: Why zeros are added at start

Sergey_Shuklin — Sat, 27 May 2017 15:27:54 GMT

Hello!

Try a Len() to indicate that values are what they appear. I mean that Len(509)=3. And if it's true you can try to use it in first iteration:

LocationID & '-' & right([Medical Record No. :],len([Medical Record No. :])) & '-' & Date as FilterID

in the second iteration (after you've got 00000509 but len([Medical Record No. :])=3):

subfield(FilterID,'-',1)&'-'&subfield(right(FilterID,len([Medical Record No. :])),'-',2)&'-'&subfield(FilterID,'-',3)

you can try to multiplicate [Medical Record No. :] by 1:

LocationID & '-' & [Medical Record No. :]*1 & '-' & Date as FilterID

use num() function

LocationID & '-' & num([Medical Record No. :],'#') & '-' & Date as FilterID

maybe some of this helps. If it wont, then we will think next