Building a record identifier

[QlikBeginner1]

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.

[RsQK]

This?

LET vStringSize = 14;
LET vStringConst = '0032';
LET vStringConstSize = 5; //Including P itself;

tempData:
LOAD
ROWNO() AS RowID
AUTOGENERATE 100000;

Data:
LOAD
RowID,
'P' & REPEAT(0,NUM('$(vStringSize)'-LEN('P' & RowID & '$(vStringConst)'))) & RowID & '$(vStringConst)' AS New
RESIDENT tempData;

DROP TABLE tempData;

[QlikBeginner1]

Hey RsQK, Thank you so much for this solution, it does work however When I try and join it to my data table, theres an error, calculation timed out? Any suggestions? Thanks again

[Kushal_Chawda]

@QlikBeginner1  how you are joining your table? n which field you are joining? Looks  like you are joining your table on non matching key between tables which actually performs the cross join and it is resource consuming as it performs join on each individual column values .

[QlikBeginner1]

As the report is split by each record by Policy Number, it would be good to have each record by Policy Number, not fussed which one it relates too. The Final for DAA is an excel sheet and its joining to a db and only showing where Policy Numbers match between the two sources.

[QlikBeginner1]

@Kushal_Chawda , any idea?

[Kushal_Chawda]

@QlikBeginner1  Why don't you just create the record identifier in final for daa table itself instead of creating  new table?

[RsQK · Accepted solution]

Either create a new field as I did in the "Final for DAA" table, or change the script to (and add it after both of these tables are loaded):

LET vStringSize = 14;
LET vStringConst = '0032';
LET vStringConstSize = 5; //Including P itself;

tempData:
LOAD DISTINCT
dpoPolicyNumber,
dpoPolicyNumber AS RowID
RESIDENT [Final for DAA];

Data:
LOAD
dpoPolicyNumber,
'P' & REPEAT(0,NUM('$(vStringSize)'-LEN('P' & RowID & '$(vStringConst)'))) & RowID & '$(vStringConst)' AS New
RESIDENT tempData;

DROP TABLE tempData;

[QlikBeginner1]

Hi @Kushal_Chawda , how would be the best way to achieve this? Apologies I am new on Qlik Sense, thanks

[Kushal_Chawda]

@QlikBeginner1  What @RsQK  has suggested should work.  You can place that logic in the table you want and rename the column so that it will be linked to other table.  You can rename it with the same column name which holds the similar values in other table. So need to join the table