Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
remyverhoeven
Contributor II
Contributor II

loop through each row of a table and create Unique ID

The problem that I have is that I have created a table without a unique ID and need to create a new unique ID based on two fields in a record. My proposed solution is to loop through each row of a table to generate a new field based on 

If(isnull(ConsultantId), InvoiceId, InvoiceId * ConsultantId)

 

Table Invoices_temp contains the following data:

InvoiceId, ConsultantId

1200, 12

1200, 13

1201, NULL

1202, NULL

1203, NULL

 

I tried this script but can not get it to work

Invoices:
Load *
Resident Invoices_temp
;


LET NumRows = NoOfRows('Invoices');

FOR i=1 to $(NumRows)
LET vInvoiceId = Peek('InvoiceId',$(i));
LET vConsultantId =Peek('ConsultantId',$(i));

If(isnull($vConsultantId), $vInvoiceId, $vInvoiceId * $vConsultantId)

NEXT;

 

I'm relatively new to Qlik and I have some troubles getting the basic syntax right of a FOR NEXT loop. Also, my calling of the variables is probably wrong. Please help!

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

So, may be all you need is this

LOAD InvoiceId,
   ConsultantId,
   RecNo() as UniqueId
FROM ...;

 

View solution in original post

4 Replies
sunny_talwar

What is the required output from the input you have provided above?
remyverhoeven
Contributor II
Contributor II
Author

The output should be a new field with a unique ID. In this example I tried to cook up a unique ID by multiplying the InvoiceId with the ConsultantId, but come to think of it any Unique Id will suffice. So putting the rownumber in is also fine.

 

The output should look something like this (in case of using row number as unique Id)

InvoiceId, ConsultantId, UniqueId

1200, 12, 1

1200, 13, 2

1201, NULL, 3

1202, NULL, 4

1203, NULL, 5

sunny_talwar

So, may be all you need is this

LOAD InvoiceId,
   ConsultantId,
   RecNo() as UniqueId
FROM ...;

 

remyverhoeven
Contributor II
Contributor II
Author

OMG, this totally worked! Hahaha, so easy! Thanks a lot!