Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cacosta35
Creator II
Creator II

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

cacosta35
Creator II
Creator II
Author

Hi!  Tamil,

It worked....Thank you so much.

Rgds