Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Data loading query

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

Re: Data loading query

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;

Re: Data loading query

Maybe try with autonumber?

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

LOAD * ;

Not applicable

Re: Data loading query

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

];

Re: Data loading query

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

Re: Data loading query

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

regards

Marco

Re: Data loading query

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

thank you..

i think the below one also works..

Autonumber (RecNo (),CarNumber) as number1_Set;

vsudhakar
Contributor III

Re: Data loading query

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

Re: Data loading query

Yes you are right, RecNo() will also work

Community Browser