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

Adding text numbers with Leading 0

Hello!  I hope someone can help me on this.  I want to add 2 fields in text  and number format.  However , I want the results in text format with leading 0 as shown below "Result"  column...Thus, the results should be in Seq field format.

I tried  Text(Num(Seq+counter,Repeat('0',Len(Seq))))  to no avail ...

   

SeqcounterResult
000410005
0032005
02103
123
213
00005200007

Thank you in advance.

14 Replies
cweiping
Contributor III
Contributor III

try it

load

No,

Seq,counter,Org

,Text('' & mid('A' & if( len(Seq)-len(text(num#(Seq)+counter))>0,Repeat('0', len(Seq)-len('' & text(num#(Seq)+counter))) & Text(num#(Seq)+counter),num#(Seq)+counter ) ,2))  as New

Resident Demo;

or

Pivot table Exp

if(len(Seq)-len(text(num#(Seq)+counter))>0,Repeat('0', len(Seq)-len(text(num#(Seq)+counter)))  & text(num#(Seq)+counter) ,'' & text(num#(Seq)+counter) )

Result_284422.png

vvira1316
Specialist II
Specialist II

Hi Caroline,

Are you looking for following... NewSeq column is calculated column. I've included script for your reference. Highlighted line is what you might be interested in.

Capture.JPG

DataTabTemp:

LOAD * Inline

[

Id, Seq, counter, Result

1, 00040, 1, 00041

2, 003, 2, 005

3, 021, 1, 022

4, 1, 2, 3

5, 2, 1, 3

6, 000059, 2, 000061

];

NoConcatenate

DataTab:

LOAD Id

,Seq

,counter

,Result

,Index(Seq, Left(Keepchar(Seq,'123456789'),1)) as NumPos

,Len(Seq) as StringLength

,Text(Repeat(Chr(48), Index(Seq, Left(Keepchar(Seq,'123456789'),1))-1)) as ZeroText

,Num#(Right(Seq, Len(Seq)-Index(Seq, Left(Keepchar(Seq,'123456789'),1))+1)) as RightNumPart

,Num#(Right(Seq, Len(Seq)-Index(Seq, Left(Keepchar(Seq,'123456789'),1))+1)) + counter as CalcResult

,Text(Repeat(Chr(48), Index(Seq, Left(Keepchar(Seq,'123456789'),1))-1)) & Text(Num#(Right(Seq, Len(Seq)-Index(Seq, Left(Keepchar(Seq,'123456789'),1))+1)) + counter) as NewSeq

Resident DataTabTemp;

DROP Table DataTabTemp;

sasiparupudi1
Master III
Master III

May be like this?

LOAD

seq,

counter,

Result,

Right('000000000000000'&(seq+counter),len(seq)) as NewResult

Inline

[

seq,counter,Result

0004,1,0005

003,2,005

02,1,03

1,2,3

2,1,3

00005,2,00007

](delimiter is ',')

;

MarcoWedel

Hi,

if your expectation is, that you might end up with identical numbers having different formats in the result field, then there is no solution.

QlikView always uses the first format a number occurs in one field for ALL instances of this number in the same field.

So its not a question about your generator expression working or not (actually your initial one does work) but rather about the way QlikView internally stores distinct values per field.

hope this helps

regards

Marco

Anonymous
Not applicable
Author

Hi!  Tamil,

It worked....Thank you so much.

Rgds