topic Re: Rowno() help in QlikView

Rowno() help

Mon, 22 Aug 2011 09:28:18 GMT

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

Re: Rowno() help

Miguel_Angel_Baeyens — Mon, 22 Aug 2011 09:39:57 GMT

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

Rowno() help

Mon, 22 Aug 2011 10:39:05 GMT

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

Re: Rowno() help

Wed, 15 May 2013 09:37:31 GMT

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

Re: Rowno() help

Wed, 15 May 2013 09:38:08 GMT

Miguel I need ur help urgent for my above post

Re: Rowno() help

Wed, 20 Aug 2014 15:18:03 GMT

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