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