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

Data loading query

I have some data which looks like

How to I write a load script such that each sequential entry for car B, for example gets an incremental number so instead of above the data would look like

etc. Any ideas much appreciated

11 Replies
qlikmsg4u
Specialist
Specialist

May be something like this?

Load Names,if(Previous(Names)=Names, Peek('Number')+1, 1)  as Number;

LOAD * INLINE [

    Names

    A

    A

    A

    A

    A

    B

    B

    B

    C

    D

    D

    E

    E

    E

    E

    F

];

Capture.PNG

Kushal_Chawda

try this,

Data:

LOAD *

FROM Table;

New:

noconcatenate

LOAD *,

         if(rowno()=1 or CarNumber<>previous(CarNumber),1,peek('Number')+1) as Number

Resident Data

order by CarNumber;

drop table Data;

settu_periasamy
Master III
Master III

Maybe try with autonumber?

Load Autonumber (CarNumber,RecNo ()) as number, *;

LOAD * ;

Not applicable
Author

TEMP:

Load *,

if(CarNumber <> previous(CarNumber), 1, peek(number)+1) as number;

Load * Inline [

CarNumber, RaceNumber, Red, speed, time

A, 110, 1, 3.06, 64.999

B, 58, 0, 85.38, 14.561

B, 125, 0, 86.56, 14.333

B, 460, 0, 83.92, 16.463

C, 197, 0, 111.9, 12.628

C, 263, 0, 112, 12.531

C, 311, 0, 111.86, 12.553

];

sunny_talwar

I think it should be

Autonumber(RowNo(), CarNumber) as number,

TEMP:

LOAD *,

  AutoNumber(RowNo(), CarNumber) as number_Sun,

  Autonumber (CarNumber,RecNo ()) as number1_Set;

LOAD * Inline [

CarNumber, RaceNumber, Red, speed, time

A, 110, 1, 3.06, 64.999

B, 58, 0, 85.38, 14.561

B, 125, 0, 86.56, 14.333

B, 460, 0, 83.92, 16.463

C, 197, 0, 111.9, 12.628

C, 263, 0, 112, 12.531

C, 311, 0, 111.86, 12.553

];


Capture.PNG

MarcoWedel

If your source table is not sorted by date, you might have to create the number field using a sorted resident load.

regards

Marco

settu_periasamy
Master III
Master III

Yes stalwar1‌. I interchanged the field and expression values.

thank you..

i think the below one also works..

Autonumber (RecNo (),CarNumber) as number1_Set;

Anonymous
Not applicable
Author

it useful by using the recno rowno functions you get the data

sunny_talwar

Yes you are right, RecNo() will also work