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: 
Anonymous
Not applicable

RowNo not reseting

Could anyone explain how to reset the RowNo after concatenating 2 tables.

I am using the RowNo as a filter and need it to reset after pushing 2 tables together.

Any help appreciated.

Many Thanks

Chris

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Final figured it,

Dropping the field RowNo and recreating it has sorted it and doing some reworking of the code based of some of your suggestions.

Many thanks all for your help.

Regards

Chris

View solution in original post

11 Replies
sunny_talwar

It is not resetting? Can you elaborate with an example and also share the script that you are using to concatenate?

marcus_sommer

Rowno() will count over all merged tables. If you want a separate counter per table then use Recno().

- Marcus

Anonymous
Not applicable
Author

Hi Sunny,

The table created in step 3 called TMP4 keeps hold of the original RowNo that I want to reset.

The process is repeated with different filters on the basket in step 1 creating a TMP4 table that is concatenated to.

Step 1.) Filter resident table Assessment to only list Basket 1 into table named TMP

TMP:

LOAD

    *,

Resident Assessment

Where ((([Category name])='Assessment') AND ((Basket)=1));

Step 2.) Use Resident TMP table to find the RowNo that are below 2.

Basket1:

NoConcatenate

LOAD *,

Where RowNo < 2;

LOAD *,

  AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

Resident TMP

Order By [P8 Points] desc, [Primary],[Subject],[Resultset],[PU],[Basket] desc;

Step 3.) Use the TMP table to find records above 1 and store in TMP4 for later

TMP4:

LOAD *,

Where RowNo > 1;

LOAD *,

  AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

Resident TMP

Order By [P8 Points] desc,[Primary],[Subject],[Resultset],[PU],[Basket] desc;

DROP Table TMP;

Anonymous
Not applicable
Author

Hi Marcus,

Cant get this to work.

To try to clarify a bit , the tables that are concatenated are holding onto the RowNo they where originally given. I want to use the concatenated table with a reset RowNo.

LOAD *,

  AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

Resident TMP4

Order By [P8 Points] desc,[Primary],[Subject],[Resultset],[PU],[Basket] desc;

Thanks

Chris

marcus_sommer

I think your naming the field RowNo was a bit misleading and I'm not really sure if I understand what do you want to do but I believe that your load-structure with preceeding loads might not quite suitable. I think your load should be more look like this:

table_0:

LOAD *,

  AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

Resident Assessment

Where ((([Category name])='Assessment') AND ((Basket)=1))

Order By [P8 Points] desc, [Primary],[Subject],[Resultset],[PU],[Basket] desc;

table_1:

Noconcatenate LOAD * resident table_0 Where RowNo < 2;

table_2:

Noconcatenate LOAD * resident table_0 Where RowNo > 1;

drop table table_0;

- Marcus

Anonymous
Not applicable
Author

Hi Marcus,

The code you have above has helped me structure my code better thanks.

I am still getting the problem of the rowno not resetting.

If I do the same code for the next basket and concatenate table_0 to table_2 the rowno stays the same. I need it to reset because I am using it further on.

Many Thanks

table_0:

LOAD *,

  AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

Resident Assessment

Where ((([Category name])='Assessment') AND ((Basket)=1))

Order By [P8 Points] desc, [Primary],[Subject],[Resultset],[PU],[Basket] desc;

table_1:

Noconcatenate LOAD * resident table_0 Where RowNo < 2;

concatenate (table_2) LOAD * resident table_0 Where RowNo > 1;

drop table table_0;

jonathandienst
Partner - Champion III
Partner - Champion III

>>I am still getting the problem of the rowno not resetting.

What do you mean by 'resetting'? If you want to have the Autonumber starting at 1 again, then you could use the optional sequence identifier field to start a new Autonumber() sequence (usually used to separate multiple Autonumbers):

     AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket], 'seq1') as RowNo    


     AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket], 'seq2') as RowNo (will start from 1 again)


But I cannot see why you would want to do this...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Hi Jonathan,

The AutoNumber only takes 2 parameters adding the seq2 creates 3.

Is this correct ?

Thanks

Chris

jonathandienst
Partner - Champion III
Partner - Champion III

No, I didn't notice that you already had two parameters, although I am still not sure what you mean by resetting the count. I suggest you post a sample qvw with some sample source data to clarify your requirements.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein