How to generate numbers

[vikasshana]

Hi Team,

I've data like below in my app

ID

123456x

5697896

9632145

964853X

3647899

From the above ID's we have two ID's which ends with 'x' and 'X', so now my requirement is I would like to have numbers from 0-9 instead of x and X. Expecting output like below.

ID
1234560
1234561
1234562
1234563
1234564
1234565
1234566
1234567
1234568
1234569
5697896
9632145
9648530
9648531
9648532
9648533
9648534
9648535
9648536
9648537
9648538
9648539
3647899

[micheledenardi · Accepted solution]

InitialTable:
NoConcatenate
load * Inline [
ID
123456x
5697896
9632145
964853X
3647899
364788X];

for i=0 to NoOfRows('InitialTable')-1
	if upper(right(Peek('ID',i,'InitialTable'),1))='X' Then
    	
        Concatenate(InitialTable)
        Load
        	left(Peek('ID',$(i),'InitialTable'),6)&(RecNo()-1) as ID
        AutoGenerate(10);
        
    End if
Next

FinalTable:
NoConcatenate
Load ID
Resident InitialTable
	Where Not WildMatch(Upper(ID),'*X')
    	Order by ID Asc;
Drop Table InitialTable;

[maxgro · Accepted solution]

T:
Load * inline [
ID
123456x
5697896
9632145
964853X
3647899
];

R:
LOAD
    ID,
    IF(MIXMATCH(RIGHT(ID, 1), 'x'),
        LEFT(ID, LEN(ID)-1) & (ITERNO()-1),
        ID
    ) AS NEW_ID
RESIDENT T
WHILE
    (MIXMATCH(RIGHT(ID, 1), 'x') AND IterNo() <= 10)
    OR ( MIXMATCH(RIGHT(ID, 1), 'x')=0 AND IterNo() <= 1)
; 

[vikasshana]

thanks @micheledenardi  and @maxgro , both the solutions are giving me the results which I'm expecting, thanks for your help.