Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
BI Consultant
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.
BI Consultant
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
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 |
Miguel I need ur help urgent for my above post
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