Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this:
Data:
LOAD TalkID,
BreakID,
StoreID,
AutoNumber(RowNo(), StoreID) as OrderID_Partition
FROM
[Sample Data.xls]
(biff, embedded labels, table is InPut$);
Thats Neat..:)
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
Thank you
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
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
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 |
Then I would say Tracy's response is the right way to go
Best,
Sunny
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.