Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikBeginner1
Creator
Creator

Building a record identifier

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.

Labels (1)
11 Replies
QlikBeginner1
Creator
Creator
Author

Fantastic @RsQK , thank you so much for you're assistance! 

QlikBeginner1
Creator
Creator
Author

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!