Assign Row Numbers

[jamelmessaoud]

Hi guys,

I have some data like below

Row No.	Type	LoadID	ConsID	JobID
0	Master	600801	1090480	625720
1	Leg	600801	1090480	625910
2	Leg	600801	1090480	625911
0	Master	694092	59682135	644897
1	Leg	694092	59682135	644901
2	Leg	694092	59682135	644902
0	Master	710724	59705448	647807
1	Leg	710724	59705448	647971
2	Leg	710724	59705448	647972
3	Leg	710724	59705448	647973

Any idea how I would go about assigning the row numbers like the above example?  I have played about with the Autonumber function but I cannot figure this one out!

The type Master is always 0 and then the others I would like to autonumber.

Help!

Thanks
J

[vamsee]

Hello,

Hoping this would help

rowno() and recno()

Counters in the Load

Thanks

[jamelmessaoud]

Sorry, they didn't really tell me how to do it..

[vamsee]

Hello,

Create a new column In your table load as follows

RowNo() as Row_Number

_________________________________________

Coming to the difference RecNo() counts the records in the source and ignores any 'where' clauses if applicable.

RowNo() assigns row numbers after the table is loaded into Qlikview.

[petter]

Actually both the links you got tell you exactly how to do it... Didn't you bother to read any of them?

[shivanandk]

Hi,

Try below code.  Replace <DataSource>  with your datasource details.

Data:

Load *,

If(Type= 'Master',0,Peek('RecNO')+1) as RecNO ;

LOAD   

    "Type",

    LoadID,

    ConsID,

    JobID

FROM <DataSource>  ;

[jamelmessaoud]

Hi. Sorry I tried this code but it didn't work..  All i did was put an increment by 1 for every row where i want it to reset back to zero (as per my sample above).

Thanks

J

[jamelmessaoud]

Hi Vamsee. I've tried to get my head around this but I'm still not sure what you mean. Are you able to use my sample data above and see show me what you mean?

Thanks

[vamsee]

Hello,

If I got it right you want to assign row numbers for the records where your type is not master.

B:
NoConcatenate
Load
*,
RowNo() as Row_Number

Resident A
Where
Type<>'Master';
Concatenate(B)
Load
*,
0 as Row_Number

Resident A
Where
Type='Master';
DROP Table A;

Thanks.

[jamelmessaoud]

Hi Vamsee,

Unfortunately no. I want the sort order to reset back to zero when its a master. Like in my example so if you have a master job then it is zero. The proceeding legs would then be auto numbered.

Does that make sense?

I really appreciate your help so far

Thanks

J