topic Re: Adding text numbers with Leading 0 in QlikView

Adding text numbers with Leading 0

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Seq	counter	Result
0004	1	0005
003	2	005
02	1	03
1	2	3
2	1	3
00005	2	00007

Thank you in advance.

Re: Adding text numbers with Leading 0

vishsaggi — Fri, 08 Dec 2017 00:39:22 GMT

What is the problem with your expr? This is what i am getting when i use your expr?

LOAD *, Text(Num(Seq+counter, Repeat('0',Len(Seq)))) As Result INLINE [

Seq, counter

0004, 1

003, 2

02, 1

1, 2

2, 1

00005, 2

];

Re: Adding text numbers with Leading 0

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:10:46 GMT

Good solution, But why third one shows normally again ??

Re: Adding text numbers with Leading 0

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:11:24 GMT

Will you provide real values?

Re: Adding text numbers with Leading 0

Anonymous — Fri, 08 Dec 2017 01:18:52 GMT

Hi! Vishwarath ,

My Seq column is in text format. Anyway, How did you make it to number and keeping the leading 0s?

Tks.

Re: Adding text numbers with Leading 0

Anonymous — Fri, 08 Dec 2017 01:24:02 GMT

Hi! Anil,

Like this?

Seq	counter	Output
01	1	02
0002	1	0003
0001	1	0002
0002	1	0003
002	1	003
01	1	02
1	2	3

Re: Adding text numbers with Leading 0

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:36:18 GMT

Your expression perfectly works until unless Unique Seq is not in the list. So, Could be issue over there. Let me recommend one person who is do some good job marcowedel‌

Re: Adding text numbers with Leading 0

vishsaggi — Fri, 08 Dec 2017 02:12:23 GMT

Oops I missed it.

Re: Adding text numbers with Leading 0

tamilarasu — Fri, 08 Dec 2017 02:16:02 GMT

Hi Caroline,

Try this,

Text(Num(Num#(Seq,'#') + counter, Repeat('0',Len(Seq))))

Re: Adding text numbers with Leading 0

vishsaggi — Fri, 08 Dec 2017 02:26:42 GMT

Try this?

LOAD Text(Seq) as Seq, counter,Text(Num(Seq+counter, Repeat('0',Len(Seq)))) As Result INLINE [

Seq, counter

0004, 1

003, 2

02, 1

1, 2

2, 1

00005, 2

];

Re: Adding text numbers with Leading 0

cweiping — Fri, 08 Dec 2017 02:46:16 GMT

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;

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

Re: Adding text numbers with Leading 0

vvira1316 — Fri, 08 Dec 2017 06:36:38 GMT

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.

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;

Re: Adding text numbers with Leading 0

sasiparupudi1 — Fri, 08 Dec 2017 15:55:17 GMT

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 ',')

;

Re: Adding text numbers with Leading 0

MarcoWedel — Fri, 08 Dec 2017 21:57:36 GMT

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

Re: Adding text numbers with Leading 0

Anonymous — Fri, 08 Dec 2017 23:55:24 GMT

Hi! Tamil,

It worked....Thank you so much.

Rgds