Logic to create new field

[raju_salmon]

Hi Experts,

I have 3 fields as input (first sheet in attached excel), and i need to populate data in new field called "OrderID_Partition" using these three fields.

I have tried using below

Data:

Load

*

from Sample Data;

Load

*,

if(rowno()=1 or TalkID<>previous(TalkID) OR StoreID<>previous(StoreID),1,peek('OrderID_Partition')+1) as OrderID_Partition

Resident Data TalkID, BreakID, StoreID;

But proper result i am not getting... expected result is in sheet 2 in attached excel. Can anyone help me please.

Thanks,

Raju

[sunny_talwar]

Are you saying that there is a big difference in reload time between with and without AutoNumber? Can you share the time difference it take to reload 10 million without AutoNumber and with autonumber?

[raju_salmon]

It took 40min to reload 3  lakh records... still reload is going...

For this calculation     AutoNumber(RowNo(), StoreID) as OrderID_Partition

taking 20 min to load everything.

[raju_salmon]

No. Time difference is between below lines

AutoNumber(rowno(),StoreID)as OrderID_Partition - Taking minutes

AutoNumber(rowno(),StoreID&'-'&TalkID )as OrderID_Partition - Taking hours

[sunny_talwar]

Would you be able to share the script where you are doing this?

[raju_salmon]

First tried in this way

Data:

LOAD TalkID,

     BreakID,

     StoreID,

      AutoNumber(RowNo(), StoreID) as OrderID_Partition,

      AutoNumber(rowno(),StoreID&'-'&TalkID )as OrderID_Partition1

FROM

[Sample Data.xls]

(biff, embedded labels, table is InPut$);

Next

Data:

LOAD TalkID,

     BreakID,

     StoreID,

   StoreID&'-'&TalkID as Key

   //   AutoNumber(RowNo(), StoreID) as OrderID_Partition,

   //   AutoNumber(rowno(),StoreID&'-'&TalkID )as OrderID_Partition1

FROM

[Sample Data.xls]

(biff, embedded labels, table is InPut$);

Load

*,

AutoNumber(rowno(),Key)as OrderID_Partition1

resident Data

Taking hours for both scenarios...

[sunny_talwar]

How about just doing one autonumber at a time? Have you tried this?

Data:

LOAD TalkID,

     BreakID,

     StoreID,

     AutoNumber(RowNo(), StoreID&TalkID) as OrderID_Partition

FROM

[Sample Data.xls]

(biff, embedded labels, table is InPut$);

[raju_salmon]

Right now loading with same code.. 4L records loaded for one hour...

[raju_salmon]

Hi Tracy,

Did you try for good performance when you got these kind of requirements? For me it is taking much time to load 10 million records.

Thanks.

[sunny_talwar]

Don't know if there are performance benefits or not, but you can try this:

Table:

LOAD TalkID,

    BreakID,

    StoreID,

    OrderID_Partition as Check

FROM

[https://community.qlik.com/thread/232515]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If(TalkID = Previous(TalkID) and StoreID = Previous(StoreID), RangeSum(Peek('OrderID_Partition'), 1), 1) as OrderID_Partition

Resident Table

Order By TalkID, StoreID, BreakID;

DROP Table Table;

[raju_salmon]

Thanks a lot for your time Sunny, looks like performance is slightly better... i will look for alternates even..