Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
It is not resetting? Can you elaborate with an example and also share the script that you are using to concatenate?
Rowno() will count over all merged tables. If you want a separate counter per table then use Recno().
- Marcus
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;
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
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
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;
>>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...
Hi Jonathan,
The AutoNumber only takes 2 parameters adding the seq2 creates 3.
Is this correct ?
Thanks
Chris
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.