topic Re: Data loading query in QlikView

Data loading query

Mon, 09 May 2016 15:51:11 GMT

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

Re: Data loading query

qlikmsg4u — Mon, 09 May 2016 16:50:05 GMT

May be something like this?

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

LOAD * INLINE [

Names

];

Re: Data loading query

Kushal_Chawda — Mon, 09 May 2016 16:58:47 GMT

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

settu_periasamy — Mon, 09 May 2016 17:02:02 GMT

Maybe try with autonumber?

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

LOAD * ;

Re: Data loading query

Mon, 09 May 2016 17:37:59 GMT

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

sunny_talwar — Mon, 09 May 2016 18:55:08 GMT

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

];

Re: Data loading query

MarcoWedel — Mon, 09 May 2016 22:46:42 GMT

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

settu_periasamy — Tue, 10 May 2016 03:31:50 GMT

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

thank you..

i think the below one also works..

Autonumber (RecNo (),CarNumber) as number1_Set;

Re: Data loading query

Anonymous — Tue, 10 May 2016 04:34:03 GMT

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

Re: Data loading query

sunny_talwar — Tue, 10 May 2016 08:11:35 GMT

Yes you are right, RecNo() will also work

Re: Data loading query

Tue, 10 May 2016 15:39:20 GMT

My original data has date and time as one field, I could split this out but don't want to, how could I make the numbering restart for second day so might bet number 1-6 for day 1 and number 1-4 for day 2 for the same car?

Re: Data loading query

sunny_talwar — Tue, 10 May 2016 17:42:37 GMT

May be like this:

Autonumber(RowNo(), CarNumber&Floor(DateTime)) as number,