Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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) )
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;
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 ',')
;
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
Hi! Tamil,
It worked....Thank you so much.
Rgds