Rowno() help

[#1]

Hi Guys,

I want to add a function in the script that adds a row number for each record in the table that it is defined. How would I do this. I seem to get the same row number appearing multiple times when I use rowno() in the script. I can't upload a QV document as this model has some confidential material. Let me know if there is anything else that you need from me to assist in clarification.

Cheers,

Byron

[Miguel_Angel_Baeyens · Accepted solution]

Hi Byron,

If you don't have any issues concatenating, joining or so, RowNo() should work just fine, returning "1" for the first record loaded, and increasing by 1 for each record onwards in the same table meaning that two tables will have twice the enumeration. You can use RecNo() function as well. Check the different behaviors according to the following script:

Table1: // Will add a different number per row
LOAD Chr(64 + Ceil(Rand() * 10)) AS ID,
           RowNo() AS RowNo
AUTOGENERATE 10 WHERE Peek('ID') <> 'A';
CONCATENATE
LOAD Chr(64 + Ceil(Rand() * 10)) AS ID,
           RowNo() AS RowNo
AUTOGENERATE 10;
Table2: // If the record is the same, the RecNo returned is the same
LOAD Chr(64 + Ceil(Rand() * 10)) AS ID2,
           RecNo() AS RowNo2
AUTOGENERATE 10 WHERE Peek('ID2') <> 'A';
CONCATENATE
LOAD Chr(64 + Ceil(Rand() * 10)) AS ID2,
           RecNo() AS RowNo2
AUTOGENERATE 10;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Thanks Miguel,

so strange, when I loaded rowno() again it worked. Not sure what I did differently this time lol. Thanks for your help. Will look through ur script and use to understand behaviours as mentioned. Cheers, Byron

[#4]

i have two columns one is ID and second is Date. My requirement is that i have to find out Row Num for each record and that i have done by RowNo() fuction but tha problem is that and the main requirement is that if the Id is changed then RowNo() again starts from 1,2,......

Help Needed Sample Data File is attached

Plz Its Urgent 

Original Data
Id	Date
A1	5/12/13 12:00 AM
A1	5/13/13 12:00 AM
A1	5/14/13 12:00 AM
A1	5/15/13 12:00 AM
B2	5/16/13 12:00 AM
B2	5/17/13 12:00 AM
C4	5/18/13 12:00 AM
C4	5/19/13 12:00 AM
C4	5/20/13 12:00 AM
D5	5/21/13 12:00 AM
E6	5/22/13 12:00 AM
I want That   Result From Above
Id	Date	Row No
A1	5/12/13 12:00 AM	1
A1	5/13/13 12:00 AM	2
A1	5/14/13 12:00 AM	3
A1	5/15/13 12:00 AM	4
B2	5/16/13 12:00 AM	1
B2	5/17/13 12:00 AM	2
C4	5/18/13 12:00 AM	1
C4	5/19/13 12:00 AM	2
C4	5/20/13 12:00 AM	3
D5	5/21/13 12:00 AM	1
E6	5/22/13 12:00 AM	1

[#5]

Miguel I need ur help urgent for my above post

[#6]

Dear Arsalan,

I have answer for you. Please check this script.

OriginalData:

LOAD

  Id,

  MakeDate(Left(20&SubField(Date,'/',3),4),SubField(Date,'/',1),SubField(Date,'/',2)) as Date

  ;

LOAD * INLINE [

Id, Date

A1, 5/12/13 12:00 AM

A1, 5/13/13 12:00 AM

A1, 5/14/13 12:00 AM

A1, 5/15/13 12:00 AM

B2, 5/16/13 12:00 AM

B2, 5/17/13 12:00 AM

C4, 5/18/13 12:00 AM

C4, 5/19/13 12:00 AM

C4, 5/20/13 12:00 AM

D5, 5/21/13 12:00 AM

E6, 5/22/13 12:00 AM

];

Result:

LOAD

  Id,

  Date,

  If(IsNull(Previous(Id)),1,

  If(Id = Previous(Id) and Date > Previous(Date), Peek('RowNumber')+1,

  If(Id <> Previous(Id) and Date > Previous(Date), 1))) as RowNumber

Resident OriginalData;

DROP Table OriginalData;

Best Regards,

Shenne Tannavi