Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chrisellis
Contributor

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
chrisellis
Contributor

Re: RowNo not reseting

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

11 Replies

Re: RowNo not reseting

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

Re: RowNo not reseting

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

- Marcus

chrisellis
Contributor

Re: RowNo not reseting

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;

chrisellis
Contributor

Re: RowNo not reseting

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

Re: RowNo not reseting

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

chrisellis
Contributor

Re: RowNo not reseting

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;

MVP
MVP

Re: RowNo not reseting

>>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...

chrisellis
Contributor

Re: RowNo not reseting

Hi Jonathan,

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

Is this correct ?

Thanks

Chris

MVP
MVP

Re: RowNo not reseting

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.

Community Browser