Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Rowno() help

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

Tags (1)
1 Solution

Accepted Solutions

Re: Rowno() help

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

5 Replies

Re: Rowno() help

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

Not applicable

Rowno() help

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

Not applicable

Re: Rowno() help

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
IdDate
A15/12/13 12:00 AM
A15/13/13 12:00 AM
A15/14/13 12:00 AM
A15/15/13 12:00 AM
B25/16/13 12:00 AM
B25/17/13 12:00 AM
C45/18/13 12:00 AM
C45/19/13 12:00 AM
C45/20/13 12:00 AM
D55/21/13 12:00 AM
E65/22/13 12:00 AM
I want That
  Result From Above
IdDateRow No
A15/12/13 12:00 AM1
A15/13/13 12:00 AM2
A15/14/13 12:00 AM3
A15/15/13 12:00 AM4
B25/16/13 12:00 AM1
B25/17/13 12:00 AM2
C45/18/13 12:00 AM1
C45/19/13 12:00 AM2
C45/20/13 12:00 AM3
D55/21/13 12:00 AM1
E65/22/13 12:00 AM1
Not applicable

Re: Rowno() help

Miguel I need ur help urgent for my above post

Not applicable

Re: Rowno() help

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