Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All.
I have received a task which is to basically create a report based off a number of fields of a excel file. The output the user wants also includes a field which is basically a record ID, which is a number which increases for each record.
They would like the letter P with a number of 0's until the number, and the end of each number to include 0032. For example;
P0000000010032
Record 1 will be P0000000010032, record 10 will be P0000000100032, record 18 will be P0000000180032 etc.
I have used AutoNumber(RowNo(),"Master Policy Number") but this just gives me 1 accross each record, I have tried including -1 and this just makes the number not what we want. Any suggestions would be appreciated, thanks in advance.
Fantastic @RsQK , thank you so much for you're assistance!
I have gotten the result I wanted, however for the first 9 records, they are coming through as 1,2,3,4,5,6,7,8,9. How can this be formatted via the RowCount() so that it shows as 01,02,03,04,,05,06,07,08,09?
LET vStringSize = 12;
LET vStringConst = '0032';
LET vStringConstSize = 5; //Including P itself;
tempData:
LOAD DISTINCT
dpoPolicyNumber,
ROWNO() AS RowID
RESIDENT [Final for DAA];
Data:
LOAD
dpoPolicyNumber,
'P' & REPEAT(0,NUM('$(vStringSize)'-LEN('P' & '$(vStringConst)'))) & RowID & '$(vStringConst)' AS New
RESIDENT tempData;
DROP TABLE tempData;
@RsQK Thank you for your solution!