Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
jamelmessaoud
Creator
Creator

Assign Row Numbers

Hi guys,

I have some data like below

     

Row No.TypeLoadIDConsIDJobID
0Master6008011090480625720
1Leg6008011090480625910
2Leg6008011090480625911
0Master69409259682135644897
1Leg69409259682135644901
2Leg69409259682135644902
0Master71072459705448647807
1Leg71072459705448647971
2Leg71072459705448647972
3Leg71072459705448647973

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

11 Replies
vamsee
Specialist
Specialist

Hello,

Hoping this would help

rowno() and recno()

Counters in the Load

Thanks

jamelmessaoud
Creator
Creator
Author

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

vamsee
Specialist
Specialist

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

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

shivanandk
Partner
Partner

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
Creator
Creator
Author

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
Creator
Creator
Author

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

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
Creator
Creator
Author

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