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]

Try this:

Data:

LOAD TalkID,

    BreakID,

    StoreID,

    AutoNumber(RowNo(), StoreID) as OrderID_Partition

FROM

[Sample Data.xls]

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

[rupamjyotidas]

Thats Neat..:)

[tracysmart · Accepted solution]

Hi Raju

I had a similar requirement across multiple fields and if you want to rank across both your fields, TalkID and StoreID you would enhance Sunnys answer like this

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

Tracy

[sunny_talwar]

Thank you

[raju_salmon]

Hi Sunny,

Thanks for your time and response. However i am receiving incorrect result if store ID is repeated for other talk ID.

Any suggestion, please find the attachment. Added TalkID 3 information.

Thanks

[sunny_talwar]

Although it seems that you have got the solution you were looking for, but I am not sure how my solution is any different then your expected solution my friend

I find them exactly the same

[raju_salmon]

Thanks for quick reply Sunny. Can you please check with this data and suggest.

TalkID	BreakID	StoreID	OrderID_Partition
1	1	102	1
1	2	102	2
1	3	102	3
1	4	102	4
1	5	102	5
1	6	102	6
1	7	100	1
1	8	102	7
1	9	102	8
1	10	102	9
1	11	102	10
1	12	100	2
1	13	102	11
1	14	100	3
2	15	103	1
2	16	103	2
2	17	103	3
2	18	103	4
2	19	103	5
2	20	103	6
2	21	103	7
2	22	103	8
2	23	103	9
2	24	103	10
2	25	103	11
2	26	103	12
2	27	103	13
2	28	103	14
2	29	103	15
2	30	1	1
2	31	1	2
3	1	102	1
3	2	102	2
3	3	101	1
3	4	102	3
3	5	102	4
3	6	102	5
3	7	100	1
3	8	102	6
3	9	102	7
3	10	102	8
3	11	102	9
3	12	100	2
3	13	102	10
3	14	100	3

[sunny_talwar]

‌Then I would say Tracy's response is the right way to go  

Best,

Sunny

[raju_salmon]

This calculation is taking much time as i have 10 million records. My key fields have 8 digits, so would it be better if use autonumber for TALKID and StoreID as separate fields and concatenate?

Data:

Load

autonumber(TalkID)     as TalkID_No

autonumber(SroreID)

as SroreID_No

From tab;

Load

*,

AutoNumber(rowno(),TalkID_No&'-'&SroreID_No)as OrderID_Partition1

resident  Data:

Looking for better way.